[2005] SQL ROWNUMBER and DISTINCT

[Simply Me]

Hi! I am creating a report using report viewer. I can already display the desired report except that I would like to add numbering beside my records.
Example:
1. Doe John M. Male
2. Tuesday Mark Male
1. Park Mark Female
2. Stew Lindsey Female

I saw this code

SELECT ROW_NUMBER() OVER (PARTITION BY Gender ORDER BY Age) AS [Partition by Gender], 
       FirstName, 
       Age,
       Gender 
  FROM Person

I have this code in my app

SELECT DISTINCT (IDNo), LastName, FirstName, MiddleName, Gender FROM table1

I tried to combine it like this

SELECT ROW_NUMBER() OVER (PARTITION BY Gender ORDER BY LastName) AS ROWID,DISTINCT (IDNo), LastName, FirstName, MiddleName, Gender FROM table1

but it's not working.

[jmcilhinney]

When you use the DISTINCT key word it needs to immediately follow the SELECT key word. The thing is though, all your rows are going to be distinct if they include a row number. What you'll need to do is perform one query to get your distinct data, then perform another query on that and add the row number.

That said, the DISTINCT key word applies to EVERY field in the row. Why would you have more than one row with the same IDNo?

I should also point out that this is an SQL-specific question and has nothing to do with VB.NET. As such it belongs in the Database Development forum.

[mendhak]

SET NOCOUNT ON

DECLARE @RowSet TABLE (RowNumber INT IDENTITY(1,1),  LastName VARCHAR(200), FirstName VARCHAR(200), MiddleName VARCHAR(200), Gender CHAR)

INSERT @RowSet 
SELECT IDNo, LastName, FirstName, MiddleName, Gender FROM table1


SELECT RowNumber,  IDNo, LastName, FirstName, MiddleName, Gender FROM @RowSet ORDER BY RowNumber

SET NOCOUNT OFF

[Simply Me]

That said, the DISTINCT key word applies to EVERY field in the row. Why would you have more than one row with the same IDNo?

Because my table contains different subject enrolled by the student having that IDNo.

[mendhak]

But if this table, as it seems, is just about the students, you should be normalizing your data structure and placing the subjects they've enrolled in in another table. That can be a one to many relationship, with the subjects-enrolled-in table having multiple rows for the same student ID.

[jmcilhinney]

But if this table, as it seems, is just about the students, you should be normalizing your data structure and placing the subjects they've enrolled in in another table. That can be a one to many relationship, with the subjects-enrolled-in table having multiple rows for the same student ID.

Quite so. You might have a Student table with StudentID and Name columns, a Subject table with SubjectID and Name columns and then a SubjectEnrollment table with SubjectID and StudentID columns. Each column in the SubjectEnrollment table is foreign key and both together form the primary key. Any additional information about either Subject or Student goes in just the table that relates to that entity and the many-to-many relationship embodied by the SubjectEnrollment table never has more than the two columns.

[Simply Me]

I can create another table for that and have a relationship between tables. with my existing setup, is it possible for me just to add the row_number by using distinct and row_number in my SQL?

[szlamany]

@simplyme

When you have

SELECT DISTINCT (IDNo), LastName, FirstName, MiddleName, Gender FROM table1

I notice you have ()'s around IDNO.

Why do you have these parenthesis?

Do you think that DISTINCT() is a function that contains a field?

As has been said DISTINCT is simply an optional keyword for SELECT

SELECT {TOP|DISTINCT} field1, field2 FROM...

Having all those fields duplicated in the table - IDNo, LastName, FirstName and so on - is bad table design - which leads to problems just like this.

You need two queries to accomplish this.

If you want to use these new SQL 2005 RANKING and NUMBERING features then put the SELECT DISTINCT in a subquery and SELECT FROM that.

Select ROW_NUMBER() OVER (PARTITION BY XYZ.Gender ORDER BY XYZ.LastName) AS ROWID
  ,XYZ.IDNo, XYZ.LastName, XYZ.FirstName, XYZ.MiddleName, XYZ.Gender 
  FROM (SELECT DISTINCT IDNo, LastName, FirstName, MiddleName, Gender FROM table1) XYZ

[si_the_geek]

Thread moved to Database Development forum - which is where SQL questions belong

[Simply Me]

i'll try this one zslamany and be back for any feedback.