All geek questions in one place

SQL: how to find the value of a Top String column in a group query

When grouping, there is an easy way to get the first or top value from a column. In the following example, I want the last value in [Text] to be sorted by [Date] in descending order.

SELECT [Id], MAX([Date]), TOP 1 [Text], <-- How do I do do this COUNT(*) FROM [ExampleTable] GROUP BY [Id] ORDER BY [Date] DESC

Thanks in advance.

0
source share
2 answers

If you need text matching this [Id] and [MaxDate], you can do this:

 SELECT T2.*, T1.Text FROM [ExampleTable] T1 JOIN (SELECT [Id], MAX([Date]) MaxDate, COUNT(*) Cnt FROM [ExampleTable] GROUP BY [Id]) T2 ON T1.Id = T2.Id AND T1.Date = T2.MaxDate ORDER BY [Date] DESC

if [Id] and [Date] form the primary key, you will have one line in (Id, Date), the other of many. If you really want this, either you set other restrictions, or use MAX or MIN ::

 SELECT T2.*, MAX(T1.Text) Text -- or MIN(T1.Text) FROM [ExampleTable] T1 JOIN (SELECT [Id], MAX([Date]) MaxDate, COUNT(*) Cnt FROM [ExampleTable] GROUP BY [Id]) T2 ON T1.Id = T2.Id AND T1.Date = T2.MaxDate GROUP BY T2.* ORDER BY [Date] DESC

If you want just a value for the text, you can use MAX or MIN:

 SELECT [Id], MAX([Date]), MAX([Text]), COUNT(*) FROM [ExampleTable] GROUP BY [Id] ORDER BY [Date] DESC
+1
source

If you understand correctly, there can be several dates for any given id and several texts for a given date.

If you are using Sql Server 2005

I got it to work

 DECLARE @Table TABLE( ID INT, DDATE DATETIME, VAL VARCHAR(MAX) ) INSERT INTO @Table (ID,DDATE,VAL) SELECT 1, '01 Jan 2009', '1' INSERT INTO @Table (ID,DDATE,VAL) SELECT 1, '01 Feb 2009', '2' INSERT INTO @Table (ID,DDATE,VAL) SELECT 1, '01 Feb 2009', '3' INSERT INTO @Table (ID,DDATE,VAL) SELECT 2, '01 Jan 2009', '4' SELECT ID, DDATE, VAL, ROWNUMBER FROM ( SELECT t.ID, t.DDATE, t.VAL, ROW_NUMBER() OVER (PARTITION BY t.ID ORDER BY DDATE) ROWNUMBER FROM @Table t INNER JOIN ( SELECT ID, MAX(DDATE) AS MAXDATE FROM @Table GROUP BY ID ) md ON t.ID = md.ID AND t.DDATE = md.MAXDATE ) RowNumber WHERE RowNumber.ROWNUMBER = 1
+1
source

Source: https://habr.com/ru/post/905161/

More articles:


All Articles