I want to find first,second,third,fourth highest from a column of a table

I have tried using max ,Group By and TOP for finding
first,second,third,fourth highest from a column of a table
PLs help me
Thanks in AdvanceHi
create table abhi (
col1 char(1),
col2 int
)
insert into abhi values('A',10)
insert into abhi values('A',8)
insert into abhi values('A',7)
insert into abhi values('A',5)
insert into abhi values('A',4)
insert into abhi values('B',12)
insert into abhi values('B',8)
insert into abhi values('B',6)
insert into abhi values('B',4)
insert into abhi values('B',5)
insert into abhi values('B',3)
DECLARE @.myrow INT
SET @.myrow=2 --change it for your needs
SELECT * FROM abhi a
WHERE (
SELECT COUNT(*) FROM abhi
WHERE col1 = a.col1
AND col2 > a.col2
) <@.myrow
ORDER BY col1, col2 DESC
"ABHI" <abhijitbavdhankar@.gmail.com> wrote in message
news:1129807557.453049.121480@.g14g2000cwa.googlegroups.com...
>I have tried using max ,Group By and TOP for finding
> first,second,third,fourth highest from a column of a table
> PLs help me
> Thanks in Advance
>sql

I want to find first,second,third,fourth highest from a column of a table

I have tried using max ,Group By and TOP for finding
first,second,third,fourth highest from a column of a table
PLs help me
Thanks in Advance
Hi
create table abhi (
col1 char(1),
col2 int
)
insert into abhi values('A',10)
insert into abhi values('A',8)
insert into abhi values('A',7)
insert into abhi values('A',5)
insert into abhi values('A',4)
insert into abhi values('B',12)
insert into abhi values('B',8)
insert into abhi values('B',6)
insert into abhi values('B',4)
insert into abhi values('B',5)
insert into abhi values('B',3)
DECLARE @.myrow INT
SET @.myrow=2 --change it for your needs
SELECT * FROM abhi a
WHERE (
SELECT COUNT(*) FROM abhi
WHERE col1 = a.col1
AND col2 > a.col2
) <@.myrow
ORDER BY col1, col2 DESC
"ABHI" <abhijitbavdhankar@.gmail.com> wrote in message
news:1129807557.453049.121480@.g14g2000cwa.googlegr oups.com...
>I have tried using max ,Group By and TOP for finding
> first,second,third,fourth highest from a column of a table
> PLs help me
> Thanks in Advance
>

I want to find first,second,third,fourth highest from a column of a table

I have tried using max ,Group By and TOP for finding
first,second,third,fourth highest from a column of a table
PLs help me
Thanks in AdvanceHi
create table abhi (
col1 char(1),
col2 int
)
insert into abhi values('A',10)
insert into abhi values('A',8)
insert into abhi values('A',7)
insert into abhi values('A',5)
insert into abhi values('A',4)
insert into abhi values('B',12)
insert into abhi values('B',8)
insert into abhi values('B',6)
insert into abhi values('B',4)
insert into abhi values('B',5)
insert into abhi values('B',3)
DECLARE @.myrow INT
SET @.myrow=2 --change it for your needs
SELECT * FROM abhi a
WHERE (
SELECT COUNT(*) FROM abhi
WHERE col1 = a.col1
AND col2 > a.col2
) <@.myrow
ORDER BY col1, col2 DESC
"ABHI" <abhijitbavdhankar@.gmail.com> wrote in message
news:1129807557.453049.121480@.g14g2000cwa.googlegroups.com...
>I have tried using max ,Group By and TOP for finding
> first,second,third,fourth highest from a column of a table
> PLs help me
> Thanks in Advance
>

i need to get the value of the max(date) of a table per person

I got a table SITUATION with a userID, a date and a situation.
Unique number
UserID
SitDate
Situation Int
I Need to get the last situation of the UserID
I'm stuck there with that query and it come out with the last for each
situation I only need the last situation.
Query:
SELECT DISTINCT MAX(DISTINCT SITUATION.SitDate) AS DateSit,
SITUATION.UserID, SITUATION.Situation
FROM SITUATION
GROUP BY SITUATION.PrevenuID,SITUATION.Situation
ORDER BY SITUATION.PrevenuID
Thanks for your help
EmmanuelJust a guess, and assumes that UserID/SitDate combo is "unique enough" (e.g.
includes time information). Please see http://www.aspfaq.com/5006 for help
on providing clear and complete requirements.
SELECT s.[Unique number],
s.UserID,
s.SitDate,
s.Situation
FROM
Situation s
INNER JOIN
(
SELECT UserID,
MAX(SitDate)
FROM Situation
GROUP BY UserID
) sub
ON
sub.UserID = s.UserID
AND s.SiteDate = sub.SiteDate;
"Emmanuel Vandal" <evandal@.videotron.com> wrote in message
news:Ft4Ef.46134$rX.677832@.weber.videotron.net...
>I got a table SITUATION with a userID, a date and a situation.
> Unique number
> UserID
> SitDate
> Situation Int
> I Need to get the last situation of the UserID
> I'm stuck there with that query and it come out with the last for each
> situation I only need the last situation.
> Query:
> SELECT DISTINCT MAX(DISTINCT SITUATION.SitDate) AS DateSit,
> SITUATION.UserID, SITUATION.Situation
> FROM SITUATION
> GROUP BY SITUATION.PrevenuID,SITUATION.Situation
> ORDER BY SITUATION.PrevenuID
> Thanks for your help
>
> Emmanuel
>|||Thank you, The "Unique enough" term just solve my problem, I generate many
data with a code that put the same date everywhere so the query was good but
the data not.
Emmanuel
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> a crit dans le
message de news: ujhUna0JGHA.3936@.TK2MSFTNGP10.phx.gbl...
> Just a guess, and assumes that UserID/SitDate combo is "unique enough"
> (e.g. includes time information). Please see http://www.aspfaq.com/5006
> for help on providing clear and complete requirements.
>
> SELECT s.[Unique number],
> s.UserID,
> s.SitDate,
> s.Situation
> FROM
> Situation s
> INNER JOIN
> (
> SELECT UserID,
> MAX(SitDate)
> FROM Situation
> GROUP BY UserID
> ) sub
> ON
> sub.UserID = s.UserID
> AND s.SiteDate = sub.SiteDate;
>
> "Emmanuel Vandal" <evandal@.videotron.com> wrote in message
> news:Ft4Ef.46134$rX.677832@.weber.videotron.net...
>