Filtering Multiple duplicate Record

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

Filtering Multiple duplicate Record

Author

Topic

cocofuc25
Starting Member

14 Posts

Posted - 2014-05-02 : 12:01:15

Hi all

im new in SQL programming world, but because one reason i think i should take a time to learn about SQL programming,

the following is query that i had created

select interfaces.nodeid as 'Node Id',
nodes.caption as 'Node Name',
netflowsources.Lasttime as NetflowLastTime

from interfaces inner join nodes on interfaces.nodeid = nodes.nodeid 
inner join netflowsources on interfaces.interfaceid = netflowsources.interfaceid

where netflowsources.LastTime NOT LIKE GETDATE()

which is from that query i get a return successfully, but i just noticed that, for coloumn nodeid was showed me multiple duplicated records, for example

nodeid value = a,a,a,b,b,c,c,c,c,d,d,d

but what i expected was to get a return without any duplicate record within it. and i also have tried using "distinct" command, but that only impacted on "a" value, but others value not change at all

is there any other command that i could tried so what i want could be reached

thanks

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-05-02 : 16:17:37

I thought I posted a reply to this, but the dog must have eaten my reply. In any case, here is what I was trying to say.

SELECT [Node Id],[Node Name]
FROM
(

select interfaces.nodeid as 'Node Id',
nodes.caption as 'Node Name',
netflowsources.Lasttime as NetflowLastTime,
ROW_NUMBER() OVER (PARTITION BY interfaces.nodeid ORDER BY netflowsources.Lasttime DESC) AS RN

from interfaces inner join nodes on interfaces.nodeid = nodes.nodeid 
inner join netflowsources on interfaces.interfaceid = netflowsources.interfaceid

where netflowsources.LastTime NOT LIKE GETDATE()
) s WHERE RN = 1

However, I am not sure that this will give you what you are looking for. Since you get multiple rows when you use distinct clause, which of those multiple rows do you want to get? The above code assumes one rule - i.e., get the one with the latest Lasttime value. May be that is not what you want.

Also, the where clause "where netflowsources.LastTime NOT LIKE GETDATE()" does not seem right. Usually you use like clause with strings. Can you describe what you are trying to filter on using that where clause?

cocofuc25
Starting Member

14 Posts

Posted - 2014-05-05 : 03:13:50

thank for the reply

ok lets take my example on my first post, which is what i want for exactly is, when i execute and query the result that i need to get is like following

nodeid : a,b,c,d,...,etc

but if im using "distinct" command i only got this

nodeid : a,b,b,c,c,c,c,d,d,d

and about "where netflowsources.LastTime NOT LIKE GETDATE()" i would like to have a return which the LastTime date value is not a date of today, and using that command, i could gave me a return that im looking for, but the duplicate record still appear and i want to take the duplicate record off the result, so its only gave me as the example above,,,

nodeid : a,b,c,d,...,etc

thanks

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-05-05 : 12:14:22

Please post sample data and expected output. That will help us understand the data better, plus you'll get code that works. Here are a couple of links that can show you how to do that incase you are not familiar with how to post your data:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

cocofuc25
Starting Member

14 Posts

Posted - 2014-05-06 : 02:14:45

i had visit to those link, but i cannt get it at all, sorry for that

so, i want to give you the result after executing the query

[url=http://postimg.org/image/4oj6hp86h/]

from the image we can see there is a lot of multiple duplicate record rigth, so what i want to get is a result that only show me 1 name for each those duplicate records

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-05-06 : 08:53:31

In your screen shot, there are 2 rows for Node Id = 761 and 3 rows for Node Id = 762. If I understood you correctly, you want to get just one row instead of the total of 5 rows. If that is the case, in your result, which node id do you want to get? 761 or 762.

On the other hand, you want to get one row for each node id, you should be able to use DISTINCT. That will give you one row for node id = 761, one row for node id = 762 etc. I am assuming that the Node Name and NetflowLasttime are the same for each of the rows that have the same node id.

cocofuc25
Starting Member

14 Posts

Posted - 2014-05-07 : 07:42:09

thanks for the reply james

your second statement is what im looking for, but i have tried using "distinct" but doesnt show me what i want, or perhaps could you show me how to use DISTINCT correctly for my case

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-05-07 : 09:03:25

[code]SELECT DISTINCT
NodeId,
NodeName,
NetflowLastTime
FROM
YourTable[/code]If that gives you what appears to be duplicate rows, that means that one or more columns are different even though they appear to the same - perhaps the time column.

cocofuc25
Starting Member

14 Posts

Posted - 2014-05-07 : 12:18:11

thanks for the reply James,

if i found one coloumn that is different just like you said, could i still using the DISTINCT, or i should use another way ??

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-05-08 : 08:31:11

If distinct is not working for you because there are differences in some of the columns, then you can use the method that I had posted in my original reply.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources