How to select records without duplicate on just one field in SQL?
Try this one
SELECT country_id, country_title
FROM (SELECT country_id, country_title,
CASE
WHEN country_title=LAG(country_title, 1, 0) OVER(ORDER BY country_title) THEN 1
ELSE 0
END AS "Duplicates"
FROM tbl_countries)
WHERE "Duplicates"=0;
For using DISTINCT
keyword, you can use it like this:
SELECT DISTINCT
(SELECT min(ti.Country_id)
FROM tbl_countries ti
WHERE t.country_title = ti.country_title) As Country_id
, country_title
FROM
tbl_countries t
For using ROW_NUMBER()
, you can use it like this:
SELECT
Country_id, country_title
FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY country_title ORDER BY Country_id) As rn
FROM tbl_countries) t
WHERE rn = 1
Also with using LEFT JOIN
, you can use this:
SELECT t1.Country_id, t1.country_title
FROM tbl_countries t1
LEFT OUTER JOIN
tbl_countries t2 ON t1.country_title = t2.country_title AND t1.Country_id > t2.Country_id
WHERE
t2.country_title IS NULL
And with using of EXISTS
, you can try:
SELECT t1.Country_id, t1.country_title
FROM tbl_countries t1
WHERE
NOT EXISTS (SELECT 1
FROM tbl_countries t2
WHERE t1.country_title = t2.country_title AND t1.Country_id > t2.Country_id)
Try this:
SELECT MIN(id) AS id, title
FROM tbl_countries
GROUP BY title
DISTINCT
is the keyword
For me your query is correct
Just try to do this first
SELECT DISTINCT title,id FROM tbl_countries
Later on you can try with order by.