Member Avatar for otengkwaku

Hi guys i have a table with this data
______________
| site | cat |
______________
| my | sp |
| my | sp |
| my | cc |
| my | cc |
| yo | sp |
| yo | sp |
| yo | cc |
| yo | cc |
______________

my question is this:
what query will give me this result
______________
| site | cat |
______________
| my | sp |
| my | cc |
| yo | sp |
| yo | cc |
______________
i am very new to sql and relational database although very good in nosql databases.
i have also spent more than 3hours searching the net for answers but have got non.

Edited by otengkwaku
  • 5 Contributors
  • 6 Replies
  • 232 Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by otengkwaku

Dani AI

Generated

Several replies pointed in the right direction: the goal is a single row per (site, cat) combination rather than keeping repeated identical rows. A quick note on the thread: 's suggested placement of the uniqueness qualifier was syntactically incorrect (that qualifier applies to the whole result set, not to one column placed after another). 's suggestion to store ids instead of repeated text is good—that is the recommended long‑term solution. marked the immediate problem solved, so the notes below focus on durable cleanup and schema design.

To remove existing duplicates safely, create a new table that enforces uniqueness and populate it from the old data after normalizing values (trim/case) so "My" and " my " don't become separate rows. Example (create a dedup table, then populate it from the old table with grouping/normalization):

CREATE TABLE deduped (
  site TEXT NOT NULL,
  cat  TEXT NOT NULL,
  PRIMARY KEY (site, cat)
);

INSERT INTO deduped (site, cat)
SELECT LOWER(TRIM(site)), LOWER(TRIM(cat))
FROM original_table
GROUP BY LOWER(TRIM(site)), LOWER(TRIM(cat));

For a robust design, normalize the strings into lookup tables and use integer foreign keys with a composite primary key on the association table. Example schema pattern:

CREATE TABLE sites   (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT UNIQUE);
CREATE TABLE cats    (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT UNIQUE);
CREATE TABLE site_cat (site_id INTEGER, cat_id INTEGER, PRIMARY KEY(site_id, cat_id));

Practical cautions: always back up before destructive changes; create any unique index only after duplicates are removed; add indexes on foreign keys for performance; run normalization (TRIM/LOWER) when inserting to avoid accidental duplicates. These steps prevent the same issue from recurring as the dataset grows.

Recommended Answers

All 6 Replies

Member Avatar for hericles

From your example it looks like you want the distinct values from the table

SELECT DISTINCT COL_NAME1, COL_NAME2 FROM...

But I doubt that is it. What exactly do you need?

Member Avatar for Taywin

Use distinct keyword? Sample

Member Avatar for Member #120589

If those columns are the extent of your fields in the table, why allow duplicates in the first place? In any event, you may be better off storing the ids (maybe integers) if these fields are foreign keys.

Member Avatar for otengkwaku

I think the problem is the way I designed my table it is all wrong thanks guys it feels good to have friends who will help if even they can't thanks this is a family

Member Avatar for peeyush.budhia

SELECT site, DISTINCT cat FROM tablename;
Change tablename to the name of table you are using.

Member Avatar for otengkwaku

Thanks guys i have solved it
the answer which is in it self another question is posted here

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.