Using TOP in SQL

I have the following data in a table (example)

Field1  Field2
ABCD    A1
ABCD    Z4
ABCD    Q3
EFGH    B2
EFGH    D1
EFGH    F8
IJKL    T4
IJKL    W3
IJKL    Y6

What I require is a SQL statement that will give me only the TOP 2 items in each group of Field2 as below (and Field1 Order DESC)

Field1  Field2
IJKL    Y6
IJKL    W3
EFGH    D1
EFGH    B2
ABCD    Q3
ABCD    Z4


I've tried

SELECT TOP 2 Field1, Field2 FROM Table Order By Field2 DESC

but this gives me

IJKL    Y6
IJKL    W3

and I've tried

SELECT TOP 2 Field2, Field1 FROM Table Order by Field2 DESC

which gives

Z4    ABCD
Y6    IJKL

both of which are not what I'm after.

How can I achieve what I want (in code)?
mutrusAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
CJ_SConnect With a Mentor Commented:
One more :-/

SELECT *
FROM TBL1 a
WHERE a.field2 IN
(
 SELECT TOP 2 field2 FROM TBL1 WHERE field1 = a.field1 ORDER BY col2 DESC
)
ORDER BY a.field1 DESC, a.field2 DESC
0
 
MrRoperCommented:
Only way you can do this is a SQL statement (IF you are using microsoft SQL) is to use cursors as per example below.

Use in a Storeprocedure


set nocount on
declare @field1 as varchar(10)

declare UCursor CURSOR FOR
select distinct field1 from test order by col1 desc
Open uCursor

fetch next from UCursor  into @Field1

while @@Fetch_Status = 0
begin
     select top 2 * from test where col1 = @Field1
     fetch next from UCursor  into @Field1
end
0
 
NazdorCommented:
Try this:

SELECT TOP 2 Field1, Field2 FROM Table Order By Field2 DESC
UNION SELECT TOP 2 Field2, Field1 FROM Table Order by Field2 DESC


or (slightly more readable perhaps)

SELECT TOP 2 Field1, Field2
    FROM Table Order By Field2 DESC
UNION
SELECT TOP 2 Field2, Field1
    FROM Table Order by Field2 DESC


0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
mutrusAuthor Commented:
Nazdor - not quite. Your suggestion results in

Field1  Field2
ABCD    Z4
IJKL    Y6
Z1      ABCD
Z4      ABCD

0
 
MrRoperCommented:
Thats not going to work, in fact in T-SQL it will still only return 2 rows
0
 
CJ_SCommented:
SELECT *
FROM TBL1 a
WHERE a.field2 IN
(
 SELECT TOP 2 field2 FROM TBL1 WHERE field1 = a.field1
)
0
 
MrRoperCommented:
Thats not gonna work either..It will return all rows
0
 
selim007Commented:
i am not sure if this can be done under microsoft databases (mdb and mssql) while it can be easily done under ORACLE by using the ORDER and PARTITION BY.
so if ur database is Access then forget about it unless you wanna use some vba codes.
if you are using mssql the method suggested by MrRoper will work perfectly using cursors.
in case you are not so involved in mssql use a vb code,it will be something like:

rst1.open "select field1 from tbl1 group by field1"

do while not rst1.eof

rst2.open "select top 2 * from tbl1 where field1='"  & rst1("field1") & "'" ,yourconnection,adopendynamic,adlockoptimistic

do while not rst2.eof
list1.additem rst2("field1") & "  " & rst2("field2")
doevents
rst2.movenext
loop
rst2.close
rst1.movenext
loop
rst1.close
0
 
CJ_SCommented:
>> Thats not gonna work either..It will return all rows

MrRoper,
I suggest you try it before judging the code.

CJ
0
 
CJ_SCommented:
The sql statement I sent DOES the job.

CJ
0
 
mutrusAuthor Commented:
CJ S

your suggestion is looking close, however  think I made a mistake in my original example. My apologies to all. The following another example which I hope clarifies my ruest

Original Table:
Field1  Field2
ABCD    0001
ABCD    0002
ABCD    0003
EFGH    0001
EFGH    0002
EFGH    0003
IJKL    0001
IJKL    0002
IJKL    0003

Result I need is (and the order is important)

Field1  Field2
IJKL    0003
IJKL    0002
EFGH    0003
EFGH    0002
ABCD    0003
ABCD    0002

So as can see Field1 and field2 are in DESC order

Hope this is more clearer
0
 
CJ_SCommented:
SELECT *
FROM TBL1 a
WHERE a.field2 IN
(
  SELECT TOP 2 field2 FROM TBL1 WHERE field1 = a.field1
)
ORDER BY a.field1 DESC, a.field2 DESC
0
 
mutrusAuthor Commented:
Sorry CJ_S

that gives:     I need:

IJKL  0002      IJKL 0003
IJKL  0001      IJKL 0002
EFGH  0002      EFGH 0003
EFGH  0001      EFGH 0002
ABCD  0002      ABCD 0003
ABCD  0001      ABCD 0002

Really do appreciate your assistance and perserverence

0
 
MrRoperCommented:
I Appologise CJ_S

I did try it but got the columns the wrong way round

I eat humble pie!
0
 
MrRoperCommented:
SELECT *
FROM test a
WHERE a.col2 IN
(
 SELECT TOP 2 col2 FROM test WHERE col1 = a.col1 order by col2 desc
)
ORDER BY a.col1 DESC


Thats the one i think!

0
 
mutrusAuthor Commented:
CJ_S only one more??

Seriously I think you've got it. Will test fully and let you know soonest

BTW my pc shows you posted your comment at 03:40am PST. Are you really working that late in the morning or in another time zone. My local time is now 09:09pm. Where is PST?
0
 
CJ_SCommented:
Pacific South Time?

I live and work in the Netherlands, europe. So it's 11:16 AM here :-)

CJ
0
 
mutrusAuthor Commented:
Interesting. I live in New Zealand which for the last 45 years I assumed was in the South Pacific. PST is 17 hours behind me
0
 
CJ_SCommented:
*LOL* Then I have no idea about P(?)S(tandard)T(ime)

CJ
0
 
mutrusAuthor Commented:
OK - I'm going to work on your SQL into my prg now so will get back with results shortly
0
 
mutrusAuthor Commented:
Perfect. Thanks. For info here is my full SQL

SELECT a.ProjectNo, ProjectData.ProjectName, a.RepNo, a.RepDetails, Format$(a.RepDate,'dd/mm/yy'), a.RepIncludedInBrief, Format$(a.RepBriefedOn,'dd/mm/yy') FROM Briefings AS a INNER JOIN ProjectData ON a.ProjectNo = ProjectData.ProjectID WHERE (((a.RepNo) In (SELECT TOP 5 RepNo FROM briefings WHERE ProjectNo = a.ProjectNo order by RepNo desc))) ORDER BY a.ProjectNo DESC

The resulting recordset then populates a Hierarchical Flex Grid which merges the Projects in desc order and displays the last five reports in desc order for each project.

Once again thanks. A brillant answer. BTW I think PST is Pacific Standard Time which is used on West coast of USA
0
 
CJ_SCommented:
Sounds more like it!

Glad to help!

CJ
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.