Select distinct on 2 fields - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Select distinct on 2 fields

Author

Topic

jszulc
Yak Posting Veteran

66 Posts

Posted - 2008-02-02 : 00:49:32

I'm having difficulty trying to extract records using SELECT DISTINCT. I would like to display unique LOB records with associated ID. I'm doing SELECT DISTINCT L_O_B. Unfortunetly I need to display ID as well in the query. I am using ID later in my C# code to pass records between 2 pages.
I was trying this but getting WAY too many records. Any ideas how to do this?

select distinct T.ID,T.L_O_B
from dbo.Offboardv7 T
where T.L_O_B = (select max(L_O_B)
from Offboardv7
where ID = T.ID)

***********************************************************
Table:
CREATE TABLE [dbo].[Offboardv7](
[ID] [int] NOT NULL,
[SID] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LAST_NAME] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FIRST_NAME] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MIDDLE_INITIAL] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[L_O_B] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DEPT] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DEPTID] [int] NULL,
[EMP_STATUS] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PHONE] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ENTRY_TP] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NEW_COST_CENTER] [int] NULL
) ON [PRIMARY

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-02 : 01:00:26

Can you please provide some sample data?. that will make matters easy.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-02 : 04:13:27

SELECT ID, MAX(L_O_B)
FROM OffBoardv7
GROUP BY ID

E 12°55'05.25"
N 56°04'39.16"

jszulc
Yak Posting Veteran

66 Posts

Posted - 2008-02-02 : 07:16:43

Peso I tried your select statement by it returns that same # of rows as in my example. Here is the sample data for this:

ID L_O_B
----------- -------------------------
23336 RETAIL SERVICES
23337 RETAIL SERVICES
57296 TECHNOLOGY
57299 TECHNOLOGY
80110 ASSET MGMT
148030 INVESTMENTS
61274 REAL ESTATE & SECURITY
253084 TREASURY & SECURITIES
253085 TREASURY & SECURITIES
253087 TREASURY & SECURITIES
148034 INVESTMENTS
57210 TECHNOLOGY
102121 CARD SERVICES
102125 CARD SERVICES

I would like to see something like this:
23336 RETAIL SERVICES
57296 TECHNOLOGY
80110 ASSET MGMT
148030 INVESTMENTS
72971 CARD SERVICES
:
:

Imagine L_O_B column would be populating dropdown box in an application. Thank you all.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-02 : 08:34:45

Mein gott.


SELECT MIN(ID), L_O_B
FROM OffBoardv7
GROUP BY L_O_B

E 12°55'05.25"
N 56°04'39.16"

jszulc
Yak Posting Veteran

66 Posts

Posted - 2008-02-02 : 09:52:33

Great. It works just fine... Thank You

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources