select distinct with multiple columsn

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 with multiple columsn

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-03-15 : 20:00:01

Hi,

I have a fairly simple query as seen below. The problem that I only want to select unique values (based on the long/lat combo). I want to bring back all 4 columns(city,longitude,latitude,viewDate).
I also want to mention that eliminating duplicate inserts is not what I am looking to do either.

Does anybody know how can I write this query?

Thanks very much!
mike123

SELECT TOP 450 city, longitude, latitude, viewDate FROM tblMapViews_Log

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-03-15 : 20:04:14

So where's the sample data and expected result set? With over 700 posts, you should know the drill.

You probably will need a GROUP BY with a derived table.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-03-15 : 20:24:55

Hey Tara,

Sorry I wasn't sure how simple or complex this would be. Let me type up some sample data right now for you.

Hopefully this helps!

Sample Data:

VANCOUVER -123.133000 49.250000 2007-01-18 14:21:00
VANCOUVER -123.133000 49.250000 2007-01-18 15:00:00
PERTH 115.833000 -31.933000 2007-01-18 15:18:00
VANCOUVER -123.133000 49.250000 2007-01-18 15:20:00
HERNDON -77.391100 38.942400 2007-01-18 15:27:00
GUILDFORD -.575000 51.235000 2007-01-17 08:24:00
ST. LOUIS -90.283100 38.635500 2007-01-18 15:43:00
NORTH VANCOUVER -123.067000 49.317000 2007-01-18 15:59:00
RESTON -77.344200 38.962300 2007-01-18 14:23:00
NASHVILLE -86.787800 36.153800 2007-01-17 08:32:00
NASHVILLE -86.787800 36.153800 2007-01-17 08:33:00
NASHVILLE -86.787800 36.153800 2007-01-17 08:34:00
NASHVILLE -86.787800 36.153800 2007-01-17 08:36:00
SEATTLE -122.330000 47.601400 2007-01-18 14:23:00

Desired Result Set:

(all duplicate long/lat values are filtered) If it comes down to only the viewDate column as being different, the max can be selected or if its not too much extra work on the query. Otherwise it actually is not too big of a deal which result is returned. This column MIGHT be dropped on the return.

Hope this helps.

Thanks very much!
mike123

VANCOUVER -123.133000 49.250000 2007-01-18 15:20:00
PERTH 115.833000 -31.933000 2007-01-18 15:18:00
HERNDON -77.391100 38.942400 2007-01-18 15:27:00
GUILDFORD -.575000 51.235000 2007-01-17 08:24:00
ST. LOUIS -90.283100 38.635500 2007-01-18 15:43:00
NORTH VANCOUVER -123.067000 49.317000 2007-01-18 15:59:00
RESTON -77.344200 38.962300 2007-01-18 14:23:00
NASHVILLE -86.787800 36.153800 2007-01-17 08:36:00
SEATTLE -122.330000 47.601400 2007-01-18 14:23:00

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-03-15 : 23:44:14

Perhaps it's just me, but I can't read data when it's like this, especially since no code tags were used to retain formatting. Please post insert into statements for your data into your table plus the create table statement for the table.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-15 : 23:54:35

See the perfect post here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=80658.
DDL, Sample Data, Expected result available and nicely formatted

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-03-16 : 02:17:09

Hey Guys,

Sorry about the confusing post. Here is the create table, insert values, and desired output. Please let me know if any more questions. Your helps much appreciated!

thanks,
mike123

declare @a table (city varchar(50), latitude numeric (18,6), longitude numeric (18,6), viewDate datetime)
insert @a
select 'VANCOUVER', -123.133000, 49.250000,'2007-01-18 14:21:00' union all
select 'VANCOUVER', -123.133000, 49.250000,'2007-01-18 15:00:00' union all
select 'PERTH', 115.833000, -31.933000,'2007-01-18 15:18:00' union all
select 'VANCOUVER', -123.133000, 49.250000,'2007-01-18 15:20:00' union all
select 'HERNDON', -77.391100, 38.942400,'2007-01-18 15:27:00' union all
select 'GUILDFORD', -.575000, 51.235000,'2007-01-17 08:24:00' union all
select 'ST. LOUIS', -90.283100, 38.635500,'2007-01-18 15:43:00' union all
select 'NORTH VANCOUVER', -123.067000, 49.317000,'2007-01-18 15:59:00' union all

select 'RESTON', -77.344200, 38.962300,'2007-01-18 14:21:00' union all
select 'NASHVILLE', -86.787800, 36.153800,'2007-01-17 08:32:00' union all
select 'NASHVILLE', -86.787800, 36.153800,'2007-01-17 08:33:00' union all
select 'NASHVILLE', -86.787800, 36.153800,'2007-01-17 08:34:00' union all
select 'NASHVILLE', -86.787800, 36.153800,'2007-01-17 08:36:00' union all
select 'SEATTLE', -122.330000, 47.601400,'2007-01-18 14:23:00'

select * from @a

Results should be:

select 'PERTH', 115.833000, -31.933000,'2007-01-18 15:18:00' union all
select 'VANCOUVER', -123.133000, 49.250000,'2007-01-18 15:20:00' union all
select 'HERNDON', -77.391100, 38.942400,'2007-01-18 15:27:00' union all
select 'GUILDFORD', -.575000, 51.235000,'2007-01-17 08:24:00' union all
select 'ST. LOUIS', -90.283100, 38.635500,'2007-01-18 15:43:00' union all
select 'NORTH VANCOUVER', -123.067000, 49.317000,'2007-01-18 15:59:00' union all
select 'RESTON', -77.344200, 38.962300,'2007-01-18 14:21:00' union all
select 'NASHVILLE', -86.787800, 36.153800,'2007-01-17 08:36:00' union all
select 'SEATTLE', -122.330000, 47.601400,'2007-01-18 14:23:00'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-16 : 02:34:01

[code]-- prepare sample data
declare @a table (city varchar(50), latitude numeric (18,6), longitude numeric (18,6), viewDate datetime)

insert @a
select 'VANCOUVER', -123.133000, 49.250000,'2007-01-18 14:21:00' union all
select 'VANCOUVER', -123.133000, 49.250000,'2007-01-18 15:00:00' union all
select 'PERTH', 115.833000, -31.933000,'2007-01-18 15:18:00' union all
select 'VANCOUVER', -123.133000, 49.250000,'2007-01-18 15:20:00' union all
select 'HERNDON', -77.391100, 38.942400,'2007-01-18 15:27:00' union all
select 'GUILDFORD', -.575000, 51.235000,'2007-01-17 08:24:00' union all
select 'ST. LOUIS', -90.283100, 38.635500,'2007-01-18 15:43:00' union all
select 'NORTH VANCOUVER', -123.067000, 49.317000,'2007-01-18 15:59:00' union all
select 'RESTON', -77.344200, 38.962300,'2007-01-18 14:21:00' union all
select 'NASHVILLE', -86.787800, 36.153800,'2007-01-17 08:32:00' union all
select 'NASHVILLE', -86.787800, 36.153800,'2007-01-17 08:33:00' union all
select 'NASHVILLE', -86.787800, 36.153800,'2007-01-17 08:34:00' union all
select 'NASHVILLE', -86.787800, 36.153800,'2007-01-17 08:36:00' union all
select 'SEATTLE', -122.330000, 47.601400,'2007-01-18 14:23:00'

-- Show the expected output
SELECT City,
Latitude,
Longitude,
MAX(ViewDate)
FROM @a
GROUP BY City,
Latitude,
Longitude
ORDER BY City[/code]
Peter Larsson
Helsingborg, Sweden

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-03-16 : 09:28:36

thx once again Peso!!! :) works perfect it appears

mike123

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-16 : 09:30:50

You do not need numeric (18,6),
change to numeric (10,6) instead.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources