Solved

Using TOP in SQL

Posted on 2002-07-25
22
245 Views
Last Modified: 2010-05-02
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)?
0
Comment
Question by:mutrus
  • 8
  • 7
  • 5
  • +2
22 Comments
 
LVL 1

Expert Comment

by:MrRoper
ID: 7176886
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
 
LVL 4

Expert Comment

by:Nazdor
ID: 7176907
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
 

Author Comment

by:mutrus
ID: 7176980
Nazdor - not quite. Your suggestion results in

Field1  Field2
ABCD    Z4
IJKL    Y6
Z1      ABCD
Z4      ABCD

0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 1

Expert Comment

by:MrRoper
ID: 7176983
Thats not going to work, in fact in T-SQL it will still only return 2 rows
0
 
LVL 22

Expert Comment

by:CJ_S
ID: 7177050
SELECT *
FROM TBL1 a
WHERE a.field2 IN
(
 SELECT TOP 2 field2 FROM TBL1 WHERE field1 = a.field1
)
0
 
LVL 1

Expert Comment

by:MrRoper
ID: 7177139
Thats not gonna work either..It will return all rows
0
 
LVL 2

Expert Comment

by:selim007
ID: 7177417
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
 
LVL 22

Expert Comment

by:CJ_S
ID: 7177523
>> Thats not gonna work either..It will return all rows

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

CJ
0
 
LVL 22

Expert Comment

by:CJ_S
ID: 7177834
The sql statement I sent DOES the job.

CJ
0
 

Author Comment

by:mutrus
ID: 7178491
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
 
LVL 22

Expert Comment

by:CJ_S
ID: 7178538
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
 

Author Comment

by:mutrus
ID: 7178900
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
 
LVL 1

Expert Comment

by:MrRoper
ID: 7179494
I Appologise CJ_S

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

I eat humble pie!
0
 
LVL 1

Expert Comment

by:MrRoper
ID: 7179496
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
 
LVL 22

Accepted Solution

by:
CJ_S earned 100 total points
ID: 7179584
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
 

Author Comment

by:mutrus
ID: 7179600
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
 
LVL 22

Expert Comment

by:CJ_S
ID: 7179602
Pacific South Time?

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

CJ
0
 

Author Comment

by:mutrus
ID: 7179615
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
 
LVL 22

Expert Comment

by:CJ_S
ID: 7179618
*LOL* Then I have no idea about P(?)S(tandard)T(ime)

CJ
0
 

Author Comment

by:mutrus
ID: 7179627
OK - I'm going to work on your SQL into my prg now so will get back with results shortly
0
 

Author Comment

by:mutrus
ID: 7179744
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
 
LVL 22

Expert Comment

by:CJ_S
ID: 7179746
Sounds more like it!

Glad to help!

CJ
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

789 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question