CODEWITHSUNDEEP
selectgroup-bymin
Marian Zagoruiko
Marian Zagoruiko

Reputation: 1596

Selecting min value of a group with id

Consider the following code:

DECLARE @table AS TABLE
  (
    id INT IDENTITY ,
    DATA VARCHAR(100) ,
    code CHAR(1)
  )

INSERT  INTO @table
        ( data, code )
VALUES  ( 'xasdf', 'a' ),
        ( 'aasdf', 'a' ),
        ( 'basdf', 'a' ),
        ( 'casdf', 'b' ),
        ( 'Casdf', 'c' ),
        ( NULL, NULL )

I need to get a row with minimum data grouped by the code. Can I do this without nested queries?

Basically, what I want is something like this:

SELECT TOP ( 1 )
        id ,
        MIN(data)
FROM    @table
GROUP BY code

Upvotes: 0

Views: 117

Answers (2)

Marian Zagoruiko
Marian Zagoruiko

Reputation: 1596

The short answer is NO. This is not possible.

Upvotes: 0

yurik256
yurik256

Reputation: 86

SELECT * (SELECT ROW_NUMBER() OVER(PARTITION BY code ORDER BY code DESC,data DESC) AS Row, id,DATA,code FROM @table) T where T.Row = 1

Upvotes: 2

Related Questions