Distinct (subquery) - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Distinct (subquery)

Author

Topic

vaiofoxx1
Starting Member

4 Posts

Posted - 2012-03-21 : 12:24:52

I'm trying to return three columns of information with one column being distinct values and the other two columns of data of the distinct values only. I'm using access and was wondering if the column I'm trying to get Distinct is affected by being alphanumeric.

SELECT Order Num, Trade Date,Cost Center Code,Man-Semi-Auto
FROM 2011 Report
WHERE Order Num IN (SELECT DISTINCT Order Num FROM 2011 Report);

I though this would work but it is not returning Distinct or unique entries.

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-21 : 13:02:08

post some sample data before and after

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

vaiofoxx1
Starting Member

4 Posts

Posted - 2012-03-21 : 13:41:49

Before

LTAUC6 12/1/2011 mark manual
LTACCL 12/1/2011 Chris manual
LTACU6 12/1/2011 Mark manual
LTAZY6 12/17/2011 Chris auto
LTAZY6 12/1/2011 Sam manual
LTAQR7 12/12/2011 Chris manual
LTAQR7 12/6/2011 roger manual
LTAQS1 12/1/2011 Bob manual
LTAZY6 12/1/2011 Chris manual

After
LTAUC6 12/1/2011 mark manual
LTACCL 12/1/2011 Chris manual
LTACU6 12/1/2011 Mark manual
LTAZY6 12/17/2011 Chris auto
LTAZY6 12/1/2011 Sam manual
LTAQR7 12/12/2011 Chris manual
LTAQR7 12/6/2011 roger manual
LTAQS1 12/1/2011 Bob manual
LTAZY6 12/1/2011 Chris manual

I would like it to show this: Distinct Col A with info from other three Col's.

LTAUC6 12/1/2011 mark manual
LTACCL 12/1/2011 Chris manual
LTAZY6 12/17/2011 Chris auto
LTAQR7 12/12/2011 Chris manual
LTAQS1 12/1/2011 Bob manual

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-21 : 13:48:11

OK, but do you care what data?

You data mostly shows the same data for the 3 columns for every Cola

But this is good

LTAZY6 12/17/2011 Chris auto
LTAZY6 12/1/2011 Chris manual

And you want

LTAZY6 12/17/2011 Chris auto

Is it that you wan the last date for ColA?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-21 : 13:50:45

Perhaps this:

SELECT * FROM [2011 Report] o WHERE EXISTS (
SELECT * FROM [2011 Report] i WHERE o.[Order Num] = i.[Order Num]
GROUP BY [Order Num]
HAVING o.[Trade Date] = MAX([Trade Date])
)

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

vaiofoxx1
Starting Member

4 Posts

Posted - 2012-03-21 : 15:42:41

I might be a novice but what does the i and o stand for?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-21 : 15:47:32

are you looking for access or t-sql query?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-03-21 : 15:50:40

quote:
Originally posted by vaiofoxx1

I might be a novice but what does the i and o stand for?

Those are called Table Aliases:
http://msdn.microsoft.com/en-us/library/ms187455(v=sql.90).aspx

vaiofoxx1
Starting Member

4 Posts

Posted - 2012-03-21 : 15:57:23

I'm using SQL in an access database.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-21 : 16:01:32

you may be better off posting this in Access forum then as there're slight variations in syntax between T-sql and access sql. posting here might prompt people to give t-sql query which might not work well in Access

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-21 : 16:09:07

My SQL SHOULD work in Access...and besides being Aliases....o means outer and i means inner...

You could use a and b, or the fully qualified name if you like..that's what access would do

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources