1

I'm trying to find unique rows based on three different columns

Here is the query I've written but I get an error

Syntax error (missing operator) in query expression 'row_number() OVER ( partition BY F1, F2,F3 order by F1)

Here is the query

SELECT F1, F2, F3 
FROM
   (SELECT  
        *, 
        ROW_NUMBER() OVER (PARTITION BY F1, F2, F3 ORDER BY F1) AS rn 
    FROM 
        tblName) a 
WHERE rn = 1

Can someone please figure out what's wrong with this query?

I'm using this query in a C# program to extract data from an Excel sheet using oledb. Thanks in advance.

Here is my C# code

OleDbDataAdapter data = new OleDbDataAdapter(query, conn);
data.Fill(ds);
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459

1 Answers1

0

It doesn't really make sense to try to get the row number, if you're asking SQL Sevrer to fetch rows with your 3 distinct values.

Supposing two rows do have the same 3 values in them... what row number should it return ?

What you should do is use RANK() (rather than row number) with PARTITION, and filter it with a "WHERE RANK = 1" filter.

There's a good example here: RANK

SELECT F1, F2, F3 
FROM
   (SELECT F1, F2, F3, RANK() OVER (PARTITION BY F1, F2, F3 ORDER BY F1) AS rn 
    FROM tblName) a 
WHERE rn = 1

However, RANK() is most useful when you want to find "the most recent something-or-other" across rows, after they've been grouped by these 3-distinct values.

If all you're interested in is getting a list of distinct 3-value combinations in your table, you'll find it simpler to just do a GROUP BY :

SELECT F1, F2, F3  
FROM tblName
GROUP BY F1, F2, F3  

Hope this helps.

Mike Gledhill
  • 27,846
  • 7
  • 149
  • 159