Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
275 views
in Technique[技术] by (71.8m points)

sql server - SQL Query Multiple Columns Using Distinct on One Column Only

I am trying to write a SQL query that selects multiple columns from a table with the distinct operator on one column only.

The table is simple. The columns are:

tblFruit_ID, tblFruit_FruitType, tblFruit_FruitName
int          NVarChar            Text

I am trying to select all the tblFruit_FruitType with their corresponding tblFruit_ID.

I have tried:

Select Distinct(tblFruit_FruitType), tblFruit_ID FROM tblFruit

-Returns all results, not just distinct

Select tblFruit_FruitType, tblFruit_ID FROM tblFruit Group By tblFruit_FruitType

-Errors with Column tblFruit_ID is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Select tblFruit_FruitType, tblFruit_ID FROM tblFruit Group By tblFruit_FruitType, tblFruit_ID

-Returns all results, not just distinct

I also checked out these similar posts and could not get anything to work :(

mySQL select one column DISTINCT, with corresponding other columns

SQL Server Distinct Union for one column

Hopefully this is enough information for an answer.

Thank you for your time!

EDIT (Sample Data and Desired Results)

tblFruit_ID, tblFruit_FruitType, tblFruit_FruitName
int          NVarChar            Text
1            Citrus              Orange
2            Citrus              Lime
3            Citrus              Lemon
4            Seed                Cherry
5            Seed                Banana

Results:

1            Citrus
4            Seed
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)
select * from tblFruit where
tblFruit_ID in (Select max(tblFruit_ID) FROM tblFruit group by tblFruit_FruitType)

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

2.1m questions

2.1m answers

60 comments

57.0k users

...