DISTINCT QUERY - 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 2000 Forums

Transact-SQL (2000)

DISTINCT QUERY

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-12-07 : 08:04:57

writes "Is there any way to run a distinct query and show a field that we do not want to be taken in account as distinct. For example returning the case ID with the distinct records?"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-07 : 08:12:25

Post some sample data and the result you want

Madhivanan

Failing to plan is Planning to fail

thermalnoise
Starting Member

7 Posts

Posted - 2005-12-07 : 13:25:21

Here's an example - I need to figure out the same thing.

The nasty SQL as it is now:

SELECT
LIB.Library_ID,
LIB.[Name],
LIB.UploadDate,
LibFmt.Description LibraryFormatName,
LIBTYPE.[Name] + '-' + LIB.CalendarYear + '.' + LIB.Version + '.' + GEO.Designation VersionNumber,
GEO.LongName GeoName,
AUDIT.DownloadDate
FROM
Library LIB
INNER JOIN Config CFG
ON CFG.LibraryFormat_ID = LIB.LibraryFormat_ID
AND CFG.[Org_ID] = 105
INNER JOIN LibraryFormat LibFmt
ON LibFmt.LibraryFormat_ID = LIB.LibraryFormat_ID
INNER JOIN LibraryType LIBTYPE
ON LIBTYPE.LibraryType_ID = LIBFMT.LibraryType_ID
INNER JOIN GeoArea GEO
ON GEO.GeoArea_ID = LIB.GeoArea_ID
INNER JOIN DownloadLog AUDIT
ON AUDIT.Library_ID = LIB.Library_ID
WHERE
LIB.[ExpireDate] > GetDate()
ORDER BY
LibraryFormatName, VersionNumber

The trick is that I want to return DISTINCT Library_ID. The end result is that I only want the most recent download record for a particular library (AUDIT.DownloadDate). The other columns just help describe the Library itself.

Any suggestions?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-08 : 00:52:23

Read this and post other informations
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Madhivanan

Failing to plan is Planning to fail

thermalnoise
Starting Member

7 Posts

Posted - 2005-12-08 : 09:34:32

I figured it out.

[CODE]
SELECT
distinct
LIB.Library_ID,
LIB.[Name],
LIB.UploadDate,
LibFmt.Description LibraryFormatName,
LIBTYPE.[Name] + '-' + LIB.CalendarYear + '.' + LIB.Version + '.' + GEO.Designation VersionNumber,
GEO.LongName GeoName,
(SELECT MAX(DownloadDate) FROM nuwc_DownloadLog WHERE nuwc_DownloadLog.Library_ID = LIB.Library_ID) DownloadDate
[/CODE]

With the rest of the SQL being the same...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-08 : 09:44:32

Well. If you posted sample data with expected result, you might have got answer here

Madhivanan

Failing to plan is Planning to fail

thermalnoise
Starting Member

7 Posts

Posted - 2005-12-08 : 10:30:50

Probably, but I looked at other examples on this forum yesterday, slept on it and tried this first thing this morning. You know how it goes sometimes =)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources