SQL Teaser

SQL Teaser

Post by David Wooddall-Gaine » Tue, 01 Jun 1999 04:00:00



There is a table POS with 3 columns and 4 rows as follows:

KEY     COLA     COLB
1              1            dog
2              1            cat
3              2            dog
4              2            cat

What is the SQL (6.5 or 7.0, please specify) to return

COLA              COLB
   1                       dog
   2                       dog

Thanks

 
 
 

SQL Teaser

Post by rava » Tue, 01 Jun 1999 04:00:00


try using :

    Select cola, colb from pos where colb = 'dog'

    But How did SqlServer allow the column name key?

Regards,
Ravan.


> There is a table POS with 3 columns and 4 rows as follows:

> KEY     COLA     COLB
> 1              1            dog
> 2              1            cat
> 3              2            dog
> 4              2            cat

> What is the SQL (6.5 or 7.0, please specify) to return

> COLA              COLB
>    1                       dog
>    2                       dog

> Thanks


 
 
 

SQL Teaser

Post by rava » Tue, 01 Jun 1999 04:00:00


You can also try this :

select cola,colb from pos p where 1 >= (select count(*) from pos p1
where p.colb < p1.colb)

if you are expecting this sort of a reply.

If you need a query of different sort be more specific

Regards,
Ravan


> There is a table POS with 3 columns and 4 rows as follows:

> KEY     COLA     COLB
> 1              1            dog
> 2              1            cat
> 3              2            dog
> 4              2            cat

> What is the SQL (6.5 or 7.0, please specify) to return

> COLA              COLB
>    1                       dog
>    2                       dog

> Thanks

 
 
 

SQL Teaser

Post by Rui Meleir » Tue, 01 Jun 1999 04:00:00



Look like you're looking for something like this:

SELECT animal, AVG(intelligence)
FROM pos
WHERE has_no_claws = true
ORDER BY me_or_my_wife
GROUP BY *_things
WITH ROLLUP, SIT, STAY

    PS: you should not try this in 7.0 - you might get an elephant

    Regards

    Rui Meleiro
    Maconde
    Portugal

select > There is a table POS with 3 columns and 4 rows as follows:

Quote:

> KEY     COLA     COLB
> 1              1            dog
> 2              1            cat
> 3              2            dog
> 4              2            cat

> What is the SQL (6.5 or 7.0, please specify) to return

> COLA              COLB
>    1                       dog
>    2                       dog

> Thanks

 
 
 

SQL Teaser

Post by David Wooddall-Gaine » Tue, 01 Jun 1999 04:00:00


thanks Ravan,

I'll try your suggestion a little later.

The table actually has about 75 columns and 100,000 rows. This query is a
snap in Access: group by Cola, and take first of Colb. That's the problem:
If I group by Cola in SQL Server I can't list Colb in the column list, And I
don't want to group by both Cola and Colb because then I get, essentially,
all four rows back. Obviously, neither will DISTINCT work.

I appreciate your response, Like I said I'll try it out in a few hours.


>You can also try this :

>select cola,colb from pos p where 1 >= (select count(*) from pos p1
>where p.colb < p1.colb)

>if you are expecting this sort of a reply.

>If you need a query of different sort be more specific

>Regards,
>Ravan


>> There is a table POS with 3 columns and 4 rows as follows:

>> KEY     COLA     COLB
>> 1              1            dog
>> 2              1            cat
>> 3              2            dog
>> 4              2            cat

>> What is the SQL (6.5 or 7.0, please specify) to return

>> COLA              COLB
>>    1                       dog
>>    2                       dog

>> Thanks

 
 
 

SQL Teaser

Post by Alexander Tarasu » Tue, 01 Jun 1999 04:00:00


select KEYPOS.COLA,POS.COLB
FROM
POS JOIN
(select COLA,MIN(THEKEY) KEYCOLUMN
FROM POS
GROUP BY COLA ) AS KEYPOS
ON POS.THEKEY=KEYPOS.KEYCOLUMN

HTH (in both 6.5 and 7.0)

Alexander Tarasul, MCSD

http://pages.ripco.com/~shleym


>thanks Ravan,

>I'll try your suggestion a little later.

>The table actually has about 75 columns and 100,000 rows. This query is a
>snap in Access: group by Cola, and take first of Colb. That's the problem:
>If I group by Cola in SQL Server I can't list Colb in the column list, And
I
>don't want to group by both Cola and Colb because then I get, essentially,
>all four rows back. Obviously, neither will DISTINCT work.

>I appreciate your response, Like I said I'll try it out in a few hours.


>>You can also try this :

>>select cola,colb from pos p where 1 >= (select count(*) from pos p1
>>where p.colb < p1.colb)

>>if you are expecting this sort of a reply.

>>If you need a query of different sort be more specific

>>Regards,
>>Ravan


>>> There is a table POS with 3 columns and 4 rows as follows:

>>> KEY     COLA     COLB
>>> 1              1            dog
>>> 2              1            cat
>>> 3              2            dog
>>> 4              2            cat

>>> What is the SQL (6.5 or 7.0, please specify) to return

>>> COLA              COLB
>>>    1                       dog
>>>    2                       dog

>>> Thanks

 
 
 

SQL Teaser

Post by David Wooddall-Gaine » Tue, 01 Jun 1999 04:00:00


Thank you Alexander. It worked like a charm

SELECT  POS1.POSITION_CLASS_CODE,
 HIST_POS.POSITION_TITLE
FROM HIST_POS JOIN
 (SELECT POSITION_CLASS_CODE, MIN(KEYPOSITION) KEYCOLUMN
  FROM HIST_POS
  GROUP BY POSITION_CLASS_CODE) AS POS1
 ON HIST_POS.KEYPOSITION=POS1.KEYCOLUMN

 
 
 

SQL Teaser

Post by David Wooddall-Gaine » Tue, 01 Jun 1999 04:00:00


Thanks Alexander,
        It worked likea charm!

SELECT  POS1.POSITION_CLASS_CODE,
 HIST_POS.POSITION_TITLE
FROM HIST_POS JOIN
 (SELECT POSITION_CLASS_CODE, MIN(KEYPOSITION) KEYCOLUMN
  FROM HIST_POS
  GROUP BY POSITION_CLASS_CODE) AS POS1
 ON HIST_POS.KEYPOSITION=POS1.KEYCOLUMN


>select KEYPOS.COLA,POS.COLB
>FROM
>POS JOIN
>(select COLA,MIN(THEKEY) KEYCOLUMN
>FROM POS
>GROUP BY COLA ) AS KEYPOS
>ON POS.THEKEY=KEYPOS.KEYCOLUMN

>HTH (in both 6.5 and 7.0)

>Alexander Tarasul, MCSD

>http://pages.ripco.com/~shleym