Top N from each grouping? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Top N from each grouping?

Author

Topic

phrankbooth
Posting Yak Master

162 Posts

Posted - 2007-03-05 : 17:25:34

Is it possible to return just the top row of each group within a query result?

Par example, if I have a query that returns a list of products, grouped by order date and product name and ordered by date, name desc

How can I just get back the top row within each date grouping so that I can list the latest prod order per prod?

Thank you for any tips!

--PhB

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-03-05 : 17:35:56

You would use MAX with a GROUP BY.

If you could provide a data example of what you want and what the table looks like, we'd be able to provide better help.

Tara Kizer

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-05 : 17:38:09

Since you didn't give a full sample to show you with, here's an example using the Northwind database:

select O.OrderDate, OD.*
from [orders] O
inner join [order details] OD on O.orderid = OD.orderid
where OD.OrderID = (select max(OrderID) 
    from [order details] 
    where OrderDate = O.OrderDate and ProductID = OD.ProductID)
order by Productid

phrankbooth
Posting Yak Master

162 Posts

Posted - 2007-03-05 : 17:54:54

Yup, I had just figured out that it was MAX. Thanks for the quickness though!

--PhB

phrankbooth
Posting Yak Master

162 Posts

Posted - 2007-03-05 : 17:57:45

Actually, is this a valid way of doing it? It works but I'm not sure if that will come back to byte me later on, when all I need is the qaTestSuite.TestSuiteStart to be max, but grouping won't let me do it without maxing some other fields I don't need.

select max(qaTests.TestID) as TestID, max(qaTestSuite.TestSuiteStart) as TestSuiteStart, qaTestSuite.TestInterface, qaTests.TestResult, max(qaTests.TestName) as TestName
FROM qaTestSuite INNER JOIN
qaTests ON qaTestSuite.TestSuiteID = qaTests.TestSuiteID
Group by qaTestSuite.TestInterface, qaTests.TestResult

--PhB

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-06 : 01:28:53

No.
There are no guarantees that MAX(TestSuiteStart) and MAX(TestName) will fetch from same record.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-06 : 01:40:02

And since the topic's title is TOP N, this might help you

select TestID, TestSuiteStart, TestInterface, TestResult, TestName
FROM (select qaTests.TestID, qaTestSuite.TestSuiteStart, qaTestSuite.TestInterface, qaTests.TestResult, qaTests.TestName
, ROW_NUMBER() OVER (PARTITION BY qaTestSuite.TestInterface, qaTests.TestResult ORDER BY qaTestSuite.TestSuiteStart DESC, qaTests.TestName) AS RecID
FROM qaTestSuite INNER JOIN
qaTests ON qaTestSuite.TestSuiteID = qaTests.TestSuiteID) as x
where recid in (1, 2) -- If you want top 2 within each group

Peter Larsson
Helsingborg, Sweden

phrankbooth
Posting Yak Master

162 Posts

Posted - 2007-03-06 : 09:31:16

Thank you Peso. I get this error with that query: 'ROW_NUMBER' is not a recognized function name.

What's the correct function name to use here?

Thanks again!

--PhB

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-06 : 09:50:44

That is a function available in SQL Server 2005 and above.
I thought since you are new to SQL Server (since you posted in this forum) you would start with 2005, not 2000.

Peter Larsson
Helsingborg, Sweden

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-06 : 10:40:18

quote:
Originally posted by phrankbooth

Actually, is this a valid way of doing it? It works but I'm not sure if that will come back to byte me later on, when all I need is the qaTestSuite.TestSuiteStart to be max, but grouping won't let me do it without maxing some other fields I don't need.

select max(qaTests.TestID) as TestID, max(qaTestSuite.TestSuiteStart) as TestSuiteStart, qaTestSuite.TestInterface, qaTests.TestResult, max(qaTests.TestName) as TestName
FROM qaTestSuite INNER JOIN
qaTests ON qaTestSuite.TestSuiteID = qaTests.TestSuiteID
Group by qaTestSuite.TestInterface, qaTests.TestResult

--PhB

The solution I gave you doesn't just group and then use max. It uses a subquery to specifically select the row with the max ID in the group, so all the other columns are then from that same row. Try it, it should do exactly what you want.

phrankbooth
Posting Yak Master

162 Posts

Posted - 2007-03-06 : 11:14:53

Peso, I'm on SQL 2000.

Thanks snSQL, I did try your query but I'm not sure where to use the select max withing mine, when I tried it, I got some grouping errors.

How would I implement your idea into my query?

Thank you!

--PhB

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-06 : 11:32:10

[code]SELECT q.TestID,
a.TestSuiteStart,
a.TestInterface,
q.TestResult,
q.TestName
FROM qaTests AS q
INNER JOIN (
SELECT TestSuiteID,
MAX(TestSuiteStart) AS tst
FROM qaTestSuite
GROUP BY TestSuiteID
) AS w ON w.TestSuiteID = q.TestSuiteID
INNER JOIN qaTestSuite AS a ON a.TestSuiteID = q.TestSuiteID AND a.TestSuiteStart = w.tst[/code]

Peter Larsson
Helsingborg, Sweden

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-06 : 11:32:15

I think this should do it

select qt.TestID as TestID, qts.TestSuiteStart as TestSuiteStart
    , qts.TestInterface, qt.TestResult, qt.TestName as TestName
FROM qaTestSuite qts 
INNER JOIN qaTests qt ON qts.TestSuiteID = qt.TestSuiteID
WHERE qt.TestID = (SELECT max(qt1.TestID) 
    FROM qaTests qt1 
    WHERE qt1.TestSuiteID = qts.TestSuiteID)
ORDER BY qaTestSuite.TestInterface, qaTests.TestResult

phrankbooth
Posting Yak Master

162 Posts

Posted - 2007-03-06 : 13:31:03

You're both gentlemen and scholars, and I appreciate the help, however, both these queries issue the same result. And it's what the original unMaxed query gives me, all the rows instead of just the latest date per grouping.

--PhB

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-06 : 13:43:25

You maybe should give us some sample data to work on?
It makes things a lot easier, I've heard...

Peter Larsson
Helsingborg, Sweden

phrankbooth
Posting Yak Master

162 Posts

Posted - 2007-03-07 : 11:30:25

OK Peso, here are the two tables' data in csv form and thanks again for your help:

qatestsuite:
TestSuiteID,TestSuiteStart,TestInterface,TestVersion,TestRelease,TestsSucceeded,TestsWarned,TestsFailed,TestMachine,TestClientMachine,TestLogin,TestLabel,TestCLPs,TestSuiteEnd
1,2007-02-15 11:13:10.000,Prod1,2.0.0 Release Version,Winter 1,5,10,20,http://mydomain.com/,WK05-DDQ-058,QALRCProfile,webqc,C:\myfolder\thefolder\,2007-02-15 11:13:10.000
2,2007-02-19 11:13:11.000,Prod1,2.0.0 Debug Version,Winter 1,8,5,66,http://mydomain.com/,WK05-DDQ-058,QALRCProfile,webqc,C:\myfolder\thefolder\,2007-02-15 11:13:10.000
3,2007-02-17 11:13:10.000,Prod2,2.0.0 Debug Version,Winter 2,88,5,3,http://mydomain.com/,WK05-DDQ-058,QALRCProfile,webqc,C:\myfolder\thefolder\,2007-02-15 11:13:10.000
4,2007-02-17 11:13:10.000,Prod3,2.0.0 Debug Version,Winter 3,7,6,5,http://mydomain.com/,WK05-DDQ-058,QALRCProfile,webqc,C:\myfolder\thefolder\,2007-02-15 11:13:10.000
5,2007-02-20 11:13:10.000,Prod4,2.0.0 Debug Version,Winter 3,4,3,2,http://mydomain.com/,WK05-DDQ-058,QALRCProfile,webqc,C:\myfolder\thefolder\Box\InterfaceTests\InterfaceTests\bin\Debug\InterfaceTests.vshost.exe -i LRC -f testlog -config LRCIIUser.xml ,2007-02-15 11:13:10.000

qatests:
TestID,TestSuiteID,TestIDInternal,TestStart,TestName,TestDescription,TestTier,TestNo,TestWFBTime,TestWFBCalled,TestSearches,TestSearchesTime,TestResult,TestEnd
1,1,123ABC,2007-02-15 11:13:09.950,TestCC,Testing the Logger Class.,Two,01,1899-12-30 11:13:10.000,1,4,1899-12-30 11:13:10.000,F,2007-02-15 11:13:10.000
2,2,124ABC,2007-02-14 11:26:29.000,TestDD,Testing the Logger Class.,Two,01,1899-12-30 11:13:10.000,2,3,1899-12-30 11:13:10.000,F,2007-02-15 11:13:10.000
3,3,125ABC,2007-02-16 11:34:01.000,TestAA,Testing the Logger Class.,Two,01,1899-12-30 11:13:10.000,3,6,1899-12-30 11:13:10.000,F,2007-02-15 11:13:10.000
4,4,126ABC,2007-02-16 11:39:57.000,TestBB,Testing the Logger Class.,Two,01,1899-12-30 11:13:10.000,4,4,1899-12-30 11:13:10.000,F,2007-02-15 11:13:10.000
5,5,127ABC,2007-02-17 11:46:54.000,TestEE,Testing the Logger Class.,Two,01,1899-12-30 11:13:10.000,5,7,1899-12-30 11:13:10.000,F,2007-02-15 11:13:10.000

--PhB

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-07 : 11:58:10

How about some create table statements so we can load the data and run it?

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-07 : 12:14:01

And, what do you want to group on? In the example data you gave there is one test per test suite. If you want the max test per test suite then you need more than one test per suite if you want to see meaningful results. Otherwise do you want to group on something other than test suite?

phrankbooth
Posting Yak Master

162 Posts

Posted - 2007-03-07 : 13:32:52

OK, sorry, here they are. And I want to group on qatestsuite.testinterface and group qatests.startdate. I want the latest (date) record within each grouping. Thank you, you are very kind!

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Tests_qaTestSuite]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[qaTests] DROP CONSTRAINT FK_Tests_qaTestSuite
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_qaTestMssgs_qaTests]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[qaTestMssgs] DROP CONSTRAINT FK_qaTestMssgs_qaTests
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[qaTestSuite]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[qaTestSuite]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[qaTests]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[qaTests]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[qaTestMssgs]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[qaTestMssgs]
GO

CREATE TABLE [dbo].[qaTestSuite] (
[TestSuiteID] [bigint] IDENTITY (1, 1) NOT NULL ,
[TestSuiteStart] [datetime] NULL ,
[TestInterface] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TestVersion] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TestRelease] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TestsSucceeded] [int] NULL ,
[TestsWarned] [int] NULL ,
[TestsFailed] [int] NULL ,
[TestMachine] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TestClientMachine] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TestLogin] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TestLabel] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TestCLPs] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TestSuiteEnd] [datetime] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[qaTests] (
[TestID] [int] IDENTITY (1, 1) NOT NULL ,
[TestSuiteID] [bigint] NOT NULL ,
[TestIDInternal] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TestStart] [datetime] NULL ,
[TestName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TestDescription] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TestTier] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TestNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TestWFBTime] [datetime] NULL ,
[TestWFBCalled] [int] NULL ,
[TestSearches] [int] NULL ,
[TestSearchesTime] [datetime] NULL ,
[TestResult] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TestEnd] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[qaTestMssgs] (
[TestMssgsID] [int] IDENTITY (1, 1) NOT NULL ,
[TestID] [int] NOT NULL ,
[TestMssgTime] [datetime] NULL ,
[TestMssgType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TestMessage] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[qaTestSuite] ADD
CONSTRAINT [DF_qaTestSuite_TestDateRun] DEFAULT (getdate()) FOR [TestSuiteStart],
CONSTRAINT [PK_TestSuite] PRIMARY KEY CLUSTERED
(
[TestSuiteID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[qaTests] ADD
CONSTRAINT [DF_qaTests_TestStart] DEFAULT (getdate()) FOR [TestStart],
CONSTRAINT [PK__Tests__72C60C4A] PRIMARY KEY CLUSTERED
(
[TestID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[qaTestMssgs] ADD
CONSTRAINT [DF_qaTestMssgs_TestMssgTime] DEFAULT (getdate()) FOR [TestMssgTime],
CONSTRAINT [PK__qaTestMssgs__75A278F5] PRIMARY KEY CLUSTERED
(
[TestMssgsID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[qaTests] ADD
CONSTRAINT [FK_Tests_qaTestSuite] FOREIGN KEY
(
[TestSuiteID]
) REFERENCES [dbo].[qaTestSuite] (
[TestSuiteID]
)
GO

ALTER TABLE [dbo].[qaTestMssgs] ADD
CONSTRAINT [FK_qaTestMssgs_qaTests] FOREIGN KEY
(
[TestID]
) REFERENCES [dbo].[qaTests] (
[TestID]
)
GO

--PhB

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-07 : 13:49:10

I think one of these is what you want. The first one just takes the test with the highest ID, which should also have the latest test date, but if your test dates are being changed after the test row is inserted, then you'll have to actually use the highest test date, which is what the second query does.

select qt.TestID as TestID, qts.TestSuiteStart as TestSuiteStart
    , qts.TestInterface, qt.TestResult, qt.TestName as TestName
FROM qaTestSuite qts 
INNER JOIN qaTests qt ON qts.TestSuiteID = qt.TestSuiteID
WHERE qt.TestID = (SELECT max(qt1.TestID) 
    FROM qaTestSuite qts1 
    INNER JOIN qaTests qt1 ON qts1.TestSuiteID = qt1.TestSuiteID 
    WHERE qts1.TestInterface = qts.TestInterface)
ORDER BY qts.TestInterface, qt.TestResult

select qt.TestID as TestID, qts.TestSuiteStart as TestSuiteStart
    , qts.TestInterface, qt.TestResult, qt.TestName as TestName
FROM qaTestSuite qts 
INNER JOIN qaTests qt ON qts.TestSuiteID = qt.TestSuiteID
WHERE qt.TestID = (SELECT max(qt1.TestID)
    FROM qaTestSuite qts1 
    INNER JOIN qaTests qt1 ON qts1.TestSuiteID = qt1.TestSuiteID 
    WHERE qts1.TestInterface = qts.TestInterface 
        AND qt1.TestStart = (SELECT max(TestStart) 
            FROM qaTestSuite qts2 
            INNER JOIN qaTests qt2 ON qts2.TestSuiteID = qt2.TestSuiteID 
            WHERE qts2.TestInterface = qts.TestInterface))
ORDER BY qts.TestInterface, qt.TestResult

phrankbooth
Posting Yak Master

162 Posts

Posted - 2007-03-07 : 14:47:36

Yes! Thank you!

I wish there was smiley for 'let me buy you a drink'

Very kind of you.

--PhB

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources