I have a table containing 2 columns for example. First column has unique values and the second column duplicates. Is there any way for me to select the first unique value only from the first column in relation to the second column?
For example: The results should get: Apple, Tire, and Fork only since they are the first results of the second column (category)
| Details | Category |
|---|---|
| Apple | Fruits |
| Banana | Fruits |
| Tire | Car |
| Engine | Car |
| Fork | Silverware |
| Spoon | Silverware |
| Knife | Silverware |
SQL table represent unordered sets. There is no "first" value unless a column specifies the value. If you have such a column, then you can use row_number():
select t.*
from (select t.*,
row_number() over (partition by category order by <ordering col>) as seqnum
from t
) t
where seqnum = 1;
If you don't have such a column, then you simply cannot ask such a question in a relational database. The data doesn't support the question.
Usually we can use windowing functions like ROW_NUMBER() to simplify these types of queries, however your requested record set does not have a natural sort order that could be used that would result in the output you are expecting.
The following is a simple solution that uses ROW_NUMBER(), however it will not result as you have requested:
SELECT Category, Details
FROM
(
SELECT Category, Details, row_number() over (partition by category order by details) as rn
FROM SpecificResults
) as numberedRecords
WHERE rn = 1;
Results:
| Category | Details |
|---|---|
| Car | Engine |
| Fruits | Apple |
| Silverware | Fork |
You requested an output of: Apple, Tire, and Fork
The next query might produce the expected output, because we do not specify the sort, however due to this the output is non-deterministic, that is we cannot gaurantee it, due to database internals over time or even after instantaneously repeated queries the result might be different.
There are many discussions on non-deterministic queries in SQL, have a read through this thread on SO: The order of a SQL Select statement without Order By clause
SELECT Category, details.Details
FROM SpecificResults byCategory
CROSS APPLY (
SELECT TOP 1 Details
FROM SpecificResults lookup
WHERE lookup.Category = byCategory.Category
--ORDER BY Details
) as details
GROUP BY Category, details.Details;
Results in:
| Category | Details |
|---|---|
| Car | Tire |
| Fruits | Apple |
| Silverware | Fork |
I have setup a SQL Fiddle for you to explore this further: http://sqlfiddle.com/#!18/68530/12
In the real world, your dataset will have a primary key, and in many cases that key value might be incrementally tallied, if not there may be other columns that could be used to determine the sort order that will match your expected results.
Assuming that your dataset has an integer column called Id and that column is an Identity column, then a simple change to the original query using ROW_NUMBER() will achieve the desired result:
SELECT Category, Details
FROM
(
SELECT Category, Details, row_number() over (partition by category order by Id) as rn
FROM OrderedResults
) as numberedRecords
WHERE rn = 1;
I have updated the SQL Fiddle with this variation: http://sqlfiddle.com/#!18/3f7bd/2
If there is a Created date or some other Timestamp or DateTime based column in your recordset then you you could consider those as candidates for your ORDER BY clause.
If I understand it correctly, try this -
select category, details from ( select *, row number() over (partition by category order by details) as rn from tablename) where rn = 1
