【Question】

Source: SQL “group by” question - I can't select every column

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:

selectURL, max(ID) asID fromT1 groupby URL

And all is working great.

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

selectURL, max(ID) asID, XMLfromT1 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!

A solution:

selectid, url, xml

 

from table1

 

whereid in(

 

select min(id)

 

from table1

 

groupby url)

 

selecturlMaxId.id, urlMaxId.url, table1.html from (selecturl, max(id) id fromtable1 groupbyurl) urlMaxId innerjointable1 on urlMaxId.id = table1.id

 

【Answer】

The SQL max function gets the maximum or minimum value but doesn’t get the corresponding record. When the algorithm needs group operation and a join, the code will be even more roundabout. Oracle offers keep/ top/row_number and window functions to handle this, but both are not convenient. For your question, I’m trying to handle it in esProc SPL. The Structured Process Language proves top() function to get the record containing the max or min value. For example, salary.top(amount;3) gets the 3 records containing the smallest amount values, and salary.top(-amount;1) gets the record holding the largest amount value. A one-liner is enough to solve your problem：

	A
1	=T1.group(URL).(~.top(1;ID)).conj()

T1: T1 table.

.group(URL): Group T1’s records by URL.

.(~.top(1;ID)): Get the record having the smallest ID from each group.

.conj(): Concatenate selected records from all groups.

esProc specializes in processing structured data and integration-friendly with a Java program or a reporting tool. Details are explained in How to Call an SPL Script in Java.