SQL "group by" question - I can't select every column

Question

Hi,

I have a database where each row has an id, a URL, and an XML. The IDs are unique, but URLs column can have duplicates. I need all the URLs, without duplicates, and for each URL I need the id and XML.

If I ask only for the URL and the Id, I use the following query:

select URL, max(ID) as ID from T1 group by URL

And all is working great.

But when I want also the XML, and I use the following query:

select URL, max(ID) as ID, XML from T1 group by URL

I get the following error:

ISC ERROR CODE:335544569
ISC ERROR MESSAGE:
Dynamic SQL Error
SQL error code = -104
user name required

What am I doing wrong?

Thanks,

Dikla

Thanks for the answers. I want to add an explanation:
In case of duplicate URLs, I don't care which of them will be returned. But I need to get them without duplicates, even if the XML is different between the rows. Is it possible?
Thanks!

Answer 1

Your no performing an aggregate function on the column XML, you must include it in your group by.

Remember if the data is different for every row it will not actually be able to be grouped and you won't get the results you are looking for, all be it they are the results you asked the database for :).

Answer 2

You need to reread your question and think about it.

You're asking for URLs (which can be duplicates) but you don't want duplicates and you also want the ID (which is not duplicated). Sample data:

1,http://www.google.com
2,http://www.yahoo.com
3,http://www.google.com

In this case what exactly do you want to return, bearing in mind that there are multiple IDs for google?

Same goes for the XML. If it's not unique for a given URL you need to specify how to get only one entry per URL and that's why your query doesn't work.

Answer 3

select id, url, xml from table1 where id in (select min(id) from table1 group by url)

would give you rows with duplicate urls removed (and only the first instance of duplicate urls included)

Answer 4

select urlMaxId.id, urlMaxId.url, table1.html from (select url, max(id) id from table1 group by url) urlMaxId inner join table1 on urlMaxId.id = table1.id