CODEWITHSUNDEEP
sqlsql-server-2008t-sqlgroup-by
user501211
user501211

Reputation: 465

SQL Group By - Get unique key of every type

I have a problem with this SQL Grouping.

If i have a table with 3 columns. Key is a unique key for every record. Type is the type of record and Version is the version of the type.

ID    Type      Version
------------------------
1      A          200
2      A          100
3      B          200
4      B          200
5      B          100

I need to get 1 row for each version of each type

so it would look something like

ID    Type       Version
------------------------
1      A           200
2      A           100
3      B           200
5      B           100

I know I need to group by the Type and version but I cant seem to figure out how to get the unique key for each Type and Version since they are on the same table

SELECT Type, Version FROM TableName GROUP BY Type, Version

Gets me all the unique Types and Versions, but I cant figure out how to finish off the query.

Upvotes: 1

Views: 2197

Answers (5)

Fruitbat
Fruitbat

Reputation: 774

This seems to work (if I've understood correctly):

declare @table table (ID int, theType varchar(5), theVersion varchar(5))

INSERT INTO @table (ID, theType, theVersion)
VALUES 
(1, 'A', '200'),
(2, 'A', '100'),
(3, 'B', '200'),
(4, 'B', '200'),
(5, 'B', '100')

SELECT a1.ID, a1.theType, a1.theVersion
FROM @table a1
LEFT JOIN @table a2 ON a2.theType = a1.theType AND a2.theVersion = a1.theVersion AND a2.ID < a1.ID
WHERE a2.ID IS NULL

Upvotes: 1

Diego
Diego

Reputation: 36156

you mean the unique ID? you need to use an aggregation function like MAX, MIN, SUM, AVG

you cant get an unique value. Think about it, if you have:

1   A   100
2   A   100
3   A   100

you want only one row with

A 100

but whats the logic to define the ID between 1,2 and 3? It isnt unique. That's why you need a MAX or MIN or any other aggregation function

Upvotes: 3

aF.
aF.

Reputation: 66707

It's quite simple, just do this:

select min(ID), Type, Version
from tablename
order by ID

Upvotes: 1

Phil
Phil

Reputation: 497

I'm not sure exactly what you are looking for. However, I'm going to assume you want one ID for each Type and Version. If that is what you want use: min(id) or max(id) in the select list and do not add anything to the group by

select min(id) as min_id, type, version from tablename group by type,version

or

select max(id) as max_id, type, version from tablename group by type,version

Upvotes: 1

martti d
martti d

Reputation: 2652

how about you concatenate the 2 columns and then use it as a GROUP BY parameter

SELECT ID, Type, Version, (Type + Version) FROM TableName GROUP BY (Type + Version)

Upvotes: 1

Related Questions