3

How can I do a SELECT where I get the DISTINCT result based on the FromLinkID AND ToLinkID together?

If I have a List with the following data, how could I get what I am after in LINQ?

alt text

VoodooChild
  • 9,776
  • 8
  • 66
  • 99

6 Answers6

2
SELECT  DISTINCT FromLinkId, ToLinkId
FROM    mytable

To select one record per (FromLinkId, ToLinkId) (that with the least id), assuming SQL Server:

SELECT  *
FROM    (
        SELECT  *, ROW_NUMBER() OVER (PARTITION BY FromLinkId, ToLinkId ORDER BY id) AS rn
        FROM    mytable
        ) q
WHERE   rn = 1
Quassnoi
  • 413,100
  • 91
  • 616
  • 614
  • 1
    OMG :) duh - but can I show the rest of the columns as well in the select in there? Which one (incase of distinct) would it show for the rest? – VoodooChild Nov 08 '10 at 16:09
  • @VoodooChild: you decide which one to select and I'll write you the query :) – Quassnoi Nov 08 '10 at 16:15
  • @Quassnoi: nice, that is brilliant. However I don't see `DISTINCT` keyword in your query, I am assuming `PARTITION BY` does the same thing? I guess `GROUP BY` is about the same thing? – VoodooChild Nov 08 '10 at 16:20
  • @Voodoo: yes, along with `WHERE rn = 1`. – Quassnoi Nov 08 '10 at 16:22
  • @Voodoo: `GROUP BY` shrinks all records from the group into one, analytics functions (like `ROW_NUMBER()`) don't. – Quassnoi Nov 08 '10 at 16:26
  • @Quassnoi: Thanks! any idea how I would go about doing this in LINQ? I have the `List` with the data I posted in my question and I want to get, what you helped me with, in code? – VoodooChild Nov 08 '10 at 16:27
  • @Voodoo: sorry, I'm not a `LINQ` expert :( – Quassnoi Nov 08 '10 at 16:28
  • @Quassnoi: Thanks for your help and I posted a LINQ answer which does what I was after. – VoodooChild Nov 08 '10 at 17:12
0

try this:

Select Distinct FromLinkId LinkId From MyTable 
Union 
Select Distinct ToLinkId LinkId From MyTable
Charles Bretana
  • 143,358
  • 22
  • 150
  • 216
  • Sir, this doesn't work. It gives me both rows from my sample data in the question. – VoodooChild Nov 09 '10 at 02:32
  • @Voodoo, This is what you need to get a set of distinct values from the combined set of all FromLinkId and ToLinkId values. It seems that you actually want the set of distinct pairs, or tuples, of (FromLinkId, ToLinkId) values, which case Quassnoi answer is the sql you want. – Charles Bretana Nov 09 '10 at 14:33
0
SELECT Name, FromLinkId, ToLinkId FROM MyTable GROUP BY FromLinkId, ToLinkId -- if there are more than one record with the same (FromLinkId, ToLinkId) pair, only one of them is displayed.
Kai Chan
  • 2,463
  • 1
  • 14
  • 13
0

You can do something like:

SELECT MAX(ID), MAX(Name), FromLinkID, ToLinkID FROM MyTable 
GROUP BY FromLinkID, ToLinkID

Or even:

SELECT t.* FROM
(SELECT MAX(ID) as MaxID, FromLinkID, ToLinkID FROM MyTable  
GROUP BY FromLinkID, ToLinkID) d 
LEFT JOIN MyTable t ON d.MaxID=t.ID
0

LINQ solution:

List<MyObj> distinctResults = allResults
  .GroupBy(p => new {p.FromLinkID, p.ToLinkID} )
  .Select(g => g.First())
  .ToList();

hope it helps someone.

Helpful related SO question.

Community
  • 1
  • 1
VoodooChild
  • 9,776
  • 8
  • 66
  • 99
0

List distinctResults = allResults .Select(p => new {p.FromLinkID, p.ToLinkID} ) .Distinct() .ToList();

Dave
  • 196
  • 1
  • 2
  • 8