SQL DISTINCT Syntax

[james ang]

Hi

I am new to this SQL and I using access to build the below statement.
Below is a working SQl statement but I need to output DISTINCT records.

How do I compose this syntax inorder to display DISTINCT record using this {SR.No#] field

-----------------------------------------------------------------------------------------
SELECT Sheet1.Date, Sheet1.[Club Name], Sheet1.[Problem / Symptom Description], Sheet1.[SR No#] FROM Sheet1

GROUP BY Sheet1.Date, Sheet1.[Club Name], Sheet1.[Problem / Symptom Description], Sheet1.[SR No#], Sheet1.[SR No#], Sheet1.Date

HAVING (((Sheet1.Date)>#6/1/2010# And (Sheet1.Date)<#6/30/2010#))
ORDER BY Sheet1.Date;

-----------------------------------------------------------------------------------------

[rch1231]

Hello,

Based on what you put in your post I am not sure you have the meaning of DISTINCT correct. If you are trying to get all records with a specific value in the Sheet1.[SR No#] field what you are looking for is really the WHERE clause. I am including the definitions and syntax for both at the bottom. DISTINCT gives you one line of output for each group of similar records (i.e. all records with Sheet1.[SR No#] = to specific value). If I am reading the post wrong then the syntax for what you want would be:

SELECT Sheet1.Date, Sheet1.[Club Name], Sheet1.[Problem / Symptom Description], DISTINCT Sheet1.[SR No#] FROM Sheet1

GROUP BY Sheet1.Date, Sheet1.[Club Name], Sheet1.[Problem / Symptom Description], Sheet1.[SR No#], Sheet1.[SR No#]

HAVING (((Sheet1.Date)>#6/1/2010# And (Sheet1.Date)<#6/30/2010#))
ORDER BY Sheet1.Date;

If you want all records for a specific Sheet1.[SR No#]
then you would use:

SELECT Sheet1.Date, Sheet1.[Club Name], Sheet1.[Problem / Symptom Description], Sheet1.[SR No#] FROM Sheet1
WHERE Sheet1.[SR No#]='100'   
GROUP BY Sheet1.Date, Sheet1.[Club Name], Sheet1.[Problem / Symptom Description], Sheet1.[SR No#], Sheet1.[SR No#]

HAVING (((Sheet1.Date)>#6/1/2010# And (Sheet1.Date)<#6/30/2010#))
ORDER BY Sheet1.Date;

The line
WHERE Sheet1.[SR No#]='100'
would imply output all rows (records) where Sheet1.[SR No#]='100'.

[james ang]

Not true
they are duplicate [SR No#] in the database.
with the "distinct" field it would only display 1 record.

I have the an example but I am not able to apply to my above SQL statement because of Syntax issuse.

Example
let’s say i have a table with one column containing the following 6 records:

3
3
4
5
5
6

is there a way with a single standard SQL query to count the unique records in that column? (the query should return “4″)

answer
the following sql works in MySQL, Oracle, and SQL Server:

SELECT COUNT(DISTINCT column_name)
FROM table_name
the following sql works where ever subqueries are accepted (like Microsoft Access):

SELECT COUNT(*)
FROM (SELECT DISTINCT column_name FROM table_name)
/* or */
SELECT COUNT(*)

[james ang]

So let’s say i have a table with one column containing the following 6 records:

[SR No#]='111' [Club Name], [Problem / Symptom Description],
[SR No#]='111' [Club Name], [Problem / Symptom Description],
[SR No#]='222' [Club Name], [Problem / Symptom Description],
[SR No#]='222' [Club Name], [Problem / Symptom Description],
[SR No#]='333' [Club Name], [Problem / Symptom Description],
[SR No#]='444' [Club Name], [Problem / Symptom Description],

After using the DISTINCT Command on my SQl Statement It should display

[SR No#]='111' [Club Name], [Problem / Symptom Description],
[SR No#]='222' [Club Name], [Problem / Symptom Description],
[SR No#]='333' [Club Name], [Problem / Symptom Description],
[SR No#]='444' [Club Name], [Problem / Symptom Description],

[rch1231]

OK I'm confused either in the way I am reading it or the way you are saying it.

First- What is not true?

Second- When you say a table with one column do you mean that each record only has one field that contains all of the data?

Third - And when you use the entry [Club Name] are you referring to the field called Club_Name like in MS-Access where they use the [] to enclose field and table names or does the data actually say [Club Name]? Distinct implies that every entry character in the field is distinct.

The syntax you posted will work in any of the databases you listed and in mysql if your data has distinct records.

Can your post your query, table and results for the first 10 records so I can figure out what I am miss reading?

[tesuji]

So let’s say i have a table with one column containing the following 6 records:

[SR No#]='111' [Club Name], [Problem / Symptom Description],
[SR No#]='111' [Club Name], [Problem / Symptom Description],
[SR No#]='222' [Club Name], [Problem / Symptom Description],
[SR No#]='222' [Club Name], [Problem / Symptom Description],
[SR No#]='333' [Club Name], [Problem / Symptom Description],
[SR No#]='444' [Club Name], [Problem / Symptom Description],

After using the DISTINCT Command on my SQl Statement It should display

[SR No#]='111' [Club Name], [Problem / Symptom Description],
[SR No#]='222' [Club Name], [Problem / Symptom Description],
[SR No#]='333' [Club Name], [Problem / Symptom Description],
[SR No#]='444' [Club Name], [Problem / Symptom Description],

Hello,

The solution you need deals with the question on how to eliminate duplicate rows from a table. On MS SQL Server there has been a very effective approach given by the new OLAP extensions since SQL Server 2005. You only need the new added aggregate function row_number() and the partition method (from the mighty OLAP functionality) to add an appropriate row number to every row what then easily allow to filter out duplicate rows. For I don't like the notation with those square brackets I am using simple names you can easily translate into your brackets notation.

Consider this query which makes use of the new aggregate function row_number():

select srno, clubname, problem, row_number() over (partition by srno order by srno) as ronu from sheet1 order by ronu, srno; 

/* result: 
additional row_number() column with partitioning by srno   ---V---  

[SR No#]='111' [Club Name], [Problem / Symptom Description],  1
[SR No#]='222' [Club Name], [Problem / Symptom Description],  1
[SR No#]='333' [Club Name], [Problem / Symptom Description],  1
[SR No#]='444' [Club Name], [Problem / Symptom Description],  1

[SR No#]='111' [Club Name], [Problem / Symptom Description],  2
[SR No#]='222' [Club Name], [Problem / Symptom Description],  2

(Ok, I have copied your example, however, what you wrote in brackets should be column names of a table. I am supposing so for your example query selects three columns [SR No#], [Club Name], and [Problem / Symptom Description] from table sheet1. I also omitted Sheet1.Date.)    
*/

Now it's easy to get distinct rows: You only need to select the rows with partition row number 1:

select srno, clubname, problem from (select srno, clubname, problem, row_number() over (partition by srno order by srno) as ronu from sheet1) as xyz
  where ronu = 1 order by srno;
/* result:
[SR No#]='111' [Club Name], [Problem / Symptom Description]
[SR No#]='222' [Club Name], [Problem / Symptom Description]
[SR No#]='333' [Club Name], [Problem / Symptom Description]
[SR No#]='444' [Club Name], [Problem / Symptom Description]
*/

Simple methode to filter out duplicates using OLAP extensions , isn't it?

You may carefully translate my convenient notation into your bracket-notation. However, I haven't test above code so there could be typos yet it should work for I am using this approach relatively often to delete duplicates from tables.

-- tesu