Distinct rows based on one column, yet get the other columns

[POSAPAY]

I'm trying to display from a log table records that have unique counter fields and date, and often the same IP address creates multiple entries, but I'd only want one record per IP address, and have all of it sorted by the date created.

here is what I have now, but it is still not listing unique IPs only:

SELECT * FROM (SELECT DISTINCT UserIP FROM dbo.tblReklamLog) AS C1 JOIN dbo.tblReklamLog AS C2 ON C1.UserIP = C2.UserIP WHERE ReklamKod = 'AUW1GMZU' ORDER BY DateCreated Desc

what am I doing wrong?

 

[MarkSweetland]

You're bascailly doing the same as this:

SELECT DISTINCT * 
FROM dbo.tblReklamLog
WHERE ReklamKod = 'AUW1GMZU' 
ORDER BY DateCreated Desc

Distinct IP addresses will still return multiple rows since each row contains unique column data. Can you provide sample data and an expected output?




Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[PWise]

how about

SELECT DISTINCT UserIP 
FROM dbo.tblReklamLog
WHERE ReklamKod = 'AUW1GMZU' 
ORDER BY DateCreated Desc

or

SELECT UserIP ,Min(counter fields ),Max(DateCreated )
FROM dbo.tblReklamLog
WHERE ReklamKod = 'AUW1GMZU' 
Group by UserIP 
ORDER BY DateCreated Desc

 

[POSAPAY]

MarkSweetland - thanks for your quick reply.

Sample data would be:
Table: tblReklamLog
Fields: ID, ReklamKod, UserIP, UserDomain, LinkSource, DateCreated
Data:
1, AUW1GMZU, 200.200.200.200, dsl123.somedomain.com,

http://google.com/search?hl=etc,

12/1/2011 5:30:09 AM

2, AUW1GMZU, 200.200.200.200, dsl123.somedomain.com,

http://google.com/search?hl=etctwo,

12/1/2011 5:32:49 AM

3, AUW1GMZU, 100.100.100.100, cable22.somedomain.com,

http://google.com/search?hl=etc,

1/1/2012 1:17:11 AM

4, AUW1GMZU, 100.100.100.100, cable22.somedomain.com,

http://google.com/search?hl=etctwo,

1/1/2012 1:20:11 AM

5, AUW1GMZU, 100.100.100.100, cable22.somedomain.com,

http://google.com/search?hl=etcthree,

1/1/2012 1:25:11 AM

6, AUW1GMZU, 100.100.100.111, 1.2.otherdomain.com,

http://google.com/search?hl=etcthree,

1/1/2012 2:59:08 AM


I would expect the following data to be returned:
1, AUW1GMZU, 200.200.200.200, dsl123.somedomain.com,

http://google.com/search?hl=etc,

12/1/2011 5:30:09 AM

3, AUW1GMZU, 100.100.100.100, cable22.somedomain.com,

http://google.com/search?hl=etc,

1/1/2012 1:17:11 AM

6, AUW1GMZU, 100.100.100.111, 1.2.otherdomain.com,

http://google.com/search?hl=etcthree,

1/1/2012 2:59:08 AM

Basically one record returned per UserIP.
I tried using Group By, but I'm not very knowledgable on that.

Thanks for any and all help!

 

[MarkSweetland]

Since the driver of the query is the 'first' ID of all the rows that may contain the User's IP, you could use something like this:

SELECT * 
from tblReklamLog a
	join (
		select UserIP, minId=MIN(id)
		from tblReklamLog b
		group by UserIP
	) as b 
	on a.id = b.minId

Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach