Solved

Using TOP in SQL

Posted on 2002-07-25
22
243 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
Comment Utility
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
Comment Utility
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
Comment Utility
Nazdor - not quite. Your suggestion results in

Field1  Field2
ABCD    Z4
IJKL    Y6
Z1      ABCD
Z4      ABCD

0
 
LVL 1

Expert Comment

by:MrRoper
Comment Utility
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
Comment Utility
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
Comment Utility
Thats not gonna work either..It will return all rows
0
 
LVL 2

Expert Comment

by:selim007
Comment Utility
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
Comment Utility
>> 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
Comment Utility
The sql statement I sent DOES the job.

CJ
0
 

Author Comment

by:mutrus
Comment Utility
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
Comment Utility
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:mutrus
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Pacific South Time?

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

CJ
0
 

Author Comment

by:mutrus
Comment Utility
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
Comment Utility
*LOL* Then I have no idea about P(?)S(tandard)T(ime)

CJ
0
 

Author Comment

by:mutrus
Comment Utility
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
Comment Utility
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
Comment Utility
Sounds more like it!

Glad to help!

CJ
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

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…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

763 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now