displaying sql data as 1 match

Question

I hope I can explain this clearly: I have two tables with the following structures:

TABLE A{id, source, url}
TABLE A2{matchid,matchsource,matchurl,country,lang}

and I have a join on them:

TABLE B{id,source,url,matchid,matchsource,matchurl,country,lang}

Table B is a join of A with A2 where for each record in A there are matching records with the same url in A2. Now I would like to display the data as follows eg

100 google www.google.com 200 google blog  www.gblog.com USA english
                          201 google news  www.gnews.com USA english

instead of

100 google www.google.com 200 google blog  www.gblog.com USA english
100 google www.google.com 201 google news  www.gnews.com USA english

of which there are hundreds of matching recs.

Is this possible? Since I need to display the records with the first match for each record in table A

Answer 1

I would suggest restricting your use of SQL to retrieving data only and leave the presentation of that data up to your application.

I don't doubt that you will be able to achieve that effect in raw SQL by the use of things like union all and fetch first (so that you treat the first row of a section differently), but it will put unnecessary strain on your database and be some seriously ugly SQL.

In other words, extract the data as per your "instead of" bit and just use the presentation layer (database client code) to only display the first three columns when either:

• it's the first row; or
• the URL is different from the previous row.

Answer 2

I guess it'll be possible some way, but I'm pretty sure that you wouldn't want that. Guess that you want that for performance optimization? It won't make it any better, I'd just use the way you've already found.

Answer 3

You can't do this in SQL. Sensibly that is.

This is a grouping on the first 3 columns that should be captured in the presentations/client.