Using TOP in SQL

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

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

Nazdor - not quite. Your suggestion results in

Field1  Field2
ABCD    Z4
IJKL    Y6
Z1      ABCD
Z4      ABCD

Thats not going to work, in fact in T-SQL it will still only return 2 rows

SELECT *
FROM TBL1 a
WHERE a.field2 IN
(
 SELECT TOP 2 field2 FROM TBL1 WHERE field1 = a.field1
)

Thats not gonna work either..It will return all rows

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

>> Thats not gonna work either..It will return all rows

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

CJ

The sql statement I sent DOES the job.

CJ

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

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

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

I Appologise CJ_S

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

I eat humble pie!

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!

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?

Pacific South Time?

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

CJ

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

*LOL* Then I have no idea about P(?)S(tandard)T(ime)

CJ

OK - I'm going to work on your SQL into my prg now so will get back with results shortly

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

Sounds more like it!

Glad to help!

CJ