Beginner SQL Question

[juggers]

Hi ,

table1 contains field1, field2, field3...etc

I am using the following

select disticnt field1
from table1

results comes field1 only

I want to show in the results the other fileds as well.

Regards

time is the father of truth

 

[bborissov]

SELECT [field list here] FROM ...
always returns anly fields you choose in field list, not all fields.
SELECT * FROM ...
returns all fields, even these from all joined tables

If I understood you desire, you want to view all records w/o duplicates but grouped by first field. What happens if in some of other field the data is different?
suppose you have data like this:

Field1  Field2
1         1
1         1
1         2
1         3

what result set you want? Some data (no metther what), or only non duplicates records?


1. Geting records distinct by Filed1 and dosn't care about the info in Field2 (BTW this query works if you are sure that you didn't have different data in other fields)

SELECT Field1, MAX(Field2) AS Field2 FROM MyTable 
GROUP BY Field1

2. If you want all non duplicated records, then you must group it by all non agregated field in query

SELECT Field1, Field2 FROM MyTable 
GROUP BY Field1, Field2

I hope you understood something from my Eglish




Borislav Borissov

 

[DotNetGnat]

i think this is what you need to do:

SELECT * from table1
group by field1

-DNG

 

[r937]

DNG, that will never work in sql server unless there is only one column, field1, in the table

sure, it works in mysql, but sql server isn't mysql, eh



r937.com | rudy.ca

 

[mhoyt]

Problem with this question is what to do with FIELD2, FIELD3, etc. Getting DISTINCT value for FIELD1 is trivial, but when FIELD1 is the same across multiple records, and you specify you want the other fields as well, but they are not the same across multiple records, what should be returned?

Analagous to asking for phone book to give you distinct last names (only one SMITH) but also return address and phone number fileds. Well, which SMITH's phone number should be included? And more importantly, what does it mean?

bborissov has the correct SQL syntax. I reproduced it for myself just now and will inlcude if you want to play with it, but real issue is one of meaning, given distinct FIELD1 what does FIELD2 etc. mean in this context?

USE Sandbox
CREATE TABLE MYTEST
(FIELD1 INT, FIELD2 INT)

INSERT INTO MYTEST VALUES (1,1)
INSERT INTO MYTEST VALUES (1,1)
INSERT INTO MYTEST VALUES (1,2)
INSERT INTO MYTEST VALUES (2,2)
INSERT INTO MYTEST VALUES (3,3)

SELECT * FROM MYTEST

SELECT DISTINCT(FIELD1), FIELD2
FROM MYTEST
--1,1
--1,2
--2,2
--3,3

SELECT FIELD1,FIELD2
FROM MYTEST
GROUP BY FIELD1,FIELD2
--1,1
--1,2
--2,2
--3,3

SELECT FIELD1, MAX(FIELD2) AS FIELD2
FROM MYTEST
GROUP BY FIELD1
--1,2
--2,2
--3,3
--Note GROUP BY only on Field1 OK because
--Field 2 in Aggregate

 

[r937]

mhoyt, that was an excellent post, especially the SMITH phone book analogy

would have given you a star except for one minor detail

this --

SELECT DISTINCT(FIELD1), FIELD2
FROM MYTEST
--1,1
--1,2
--2,2
--3,3

is exceedingly misleading

i cannot begin to count how many times i've run across people who think that DISTINCT is a function, and when you write a query like that, you add to the problem, not help solve it

please, don't ever write SELECT DISTINCT(FIELD1), FIELD2 ...




r937.com | rudy.ca

 

[mhoyt]

Thanks for the compliment. This was my first attempt to contribute something besides a question. Not sure what a Star is, but would help sway you to understand I was not recommending the syntax, just including it to show what the result set was? (The syntax had already been suggested and I was trying to show the results from the different suggestions made earlier)

In any case, I do feel encouraged to keep trying