Select unique country/date pairs with self join

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

General SQL Server Forums

New to SQL Server Programming

Select unique country/date pairs with self join

Author

Topic

niko79542
Starting Member

7 Posts

Posted - 2013-09-16 : 11:59:30

Hi, I have built a sample table, query, and results for this question. I am using SQL server.

declare @TableX Table
(
Date Date not null,
ID int not null,
Tick varchar(6) not null
)
INSERT @tableX
Values
('2013-09-06', 1, 'A.USA'),
('2013-09-05', 1, 'A.USA'),
('2013-09-04', 1, 'A.USA'),
('2013-09-03', 1, 'A.USA'),
('2013-09-02', 1, 'A.USA'),
('2013-09-06', 2, 'B.USA'),
('2013-09-05', 2, 'B.USA'),
('2013-09-04', 2, 'B.USA'),
('2013-09-03', 2, 'B.USA'),
('2013-09-02', 2, 'B.USA'),
('2013-09-06', 3, 'C.LON'),
('2013-09-05', 3, 'C.LON'),
('2013-09-04', 3, 'C.LON'),
('2013-09-03', 3, 'C.LON'),
('2013-09-02', 3, 'C.LON')
;

select
DATE,
RIGHT(Tick,LEN(tick)-CHARINDEX('.', tick)),
COUNT(*) as count
from @TableX
group by date, RIGHT(Tick,LEN(tick)-CHARINDEX('.', tick))

---

Expected results:
DATE (No column name) count
2013-09-02 LON 1
2013-09-03 LON 1
2013-09-04 LON 1
2013-09-05 LON 1
2013-09-06 LON 1
2013-09-02 USA 2
2013-09-03 USA 2
2013-09-04 USA 2
2013-09-05 USA 2
2013-09-06 USA 2

I want to select unique country - date pairs. It is not even necessary to have the count of each one, just the list of unique country/dates.

My query here uses 'group by' to accomplish this task, but there may be a way to do this with a self join. I believe using a self join would make the query faster.

1) Is this possible to do with a self join?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-16 : 15:00:59

If you don't need the count, you could use distinct. In either case, I don't think using a self-join would be more efficient. It is not dawning on me how to use self-join in this case. Even if you were able to, you will be scanning or seeking in the same table twice, which would be less efficient than group by or distinct.

SELECT DISTINCT
	Date,RIGHT(Tick,LEN(tick)-CHARINDEX('.', tick))
FROM
	@TableX

niko79542
Starting Member

7 Posts

Posted - 2013-09-16 : 15:14:43

Thanks James!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources