[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SELECT First Row

Posted on 2005-05-13
11
Medium Priority
?
224 Views
Last Modified: 2010-03-19
I have the query below

select  *
from RT_Weekly_2000_Item_Master_Initial_Cleaning a
where exists
(
select count(COUNTRY_RESTRICTED_ITEM),ITEM_NUMBER,ORG_ID,
from RT_Weekly_2000_Item_Master_Initial_Cleaning
where ITEM_NUMBER=a.ITEM_NUMBER
group by ITEM_NUMBER,ORG_ID
having count(COUNTRY_RESTRICTED_ITEM)>1
)

which select the term like


ITEM_NUMBER  COUNTRY_RESTRICTED_ITEM
11                                au
11                                nz
11                                sc
15                                my
15                                sin
15                                ay

but i only want

ITEM_NUMBER  COUNTRY_RESTRICTED_ITEM
11                                au
15                                my

Any ideas?

Thanx
Yee


0
Comment
Question by:mingfattt
  • 6
  • 5
11 Comments
 
LVL 11

Expert Comment

by:lluthien
ID: 13994301
select  item_number, min(country_restricted_item) as country_restricted_item
from RT_Weekly_2000_Item_Master_Initial_Cleaning a
where exists
(
select count(COUNTRY_RESTRICTED_ITEM),ITEM_NUMBER,ORG_ID,
from RT_Weekly_2000_Item_Master_Initial_Cleaning
where ITEM_NUMBER=a.ITEM_NUMBER
group by ITEM_NUMBER,ORG_ID
having count(COUNTRY_RESTRICTED_ITEM)>1
)
group by country_restricted_item
0
 
LVL 2

Author Comment

by:mingfattt
ID: 13994330
i get the following

Column 'a.ORG_ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

and so much more the same error
0
 
LVL 11

Expert Comment

by:lluthien
ID: 13994343
sorry ,

i meant this
select  item_number, min(country_restricted_item) as country_restricted_item
from RT_Weekly_2000_Item_Master_Initial_Cleaning a
where exists
(
select count(COUNTRY_RESTRICTED_ITEM),ITEM_NUMBER,ORG_ID,
from RT_Weekly_2000_Item_Master_Initial_Cleaning
where ITEM_NUMBER=a.ITEM_NUMBER
group by ITEM_NUMBER,ORG_ID
having count(COUNTRY_RESTRICTED_ITEM)>1
)
group by item_number

but i'm working on a different one,
try this first
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 11

Expert Comment

by:lluthien
ID: 13994350
jeez..

select  item_number, min(country_restricted_item) as country_restricted_item
from RT_Weekly_2000_Item_Master_Initial_Cleaning a
where exists
(
select count(COUNTRY_RESTRICTED_ITEM),ITEM_NUMBER,ORG_ID
from RT_Weekly_2000_Item_Master_Initial_Cleaning
where ITEM_NUMBER=a.ITEM_NUMBER
group by ITEM_NUMBER,ORG_ID
having count(COUNTRY_RESTRICTED_ITEM)>1
)
group by item_number,ORG_ID


typing is difficult today.. sorry about that
0
 
LVL 2

Author Comment

by:mingfattt
ID: 13994358
it works, but actually i have more field than this, i am just wondering can i put the wildcart (*) at the beginning, and FYI i m not possible to write in one by one it is just too much... thanx
0
 
LVL 11

Expert Comment

by:lluthien
ID: 13994393
everything you add into the select clause,
you also have to use in either an aggregate function ( min )
or in a group by clause

but because you are using an aggregate, the * won't work,
because then you would be selecting both country_restricted_item AND the Minimum value of the same column.

that won't work.
0
 
LVL 2

Author Comment

by:mingfattt
ID: 13994400
any suggestion to select all the column in the table? i need this badly
0
 
LVL 11

Accepted Solution

by:
lluthien earned 1000 total points
ID: 13994422
if you want to use a select *
try this:

select a.*
from RT_Weekly_2000_Item_Master_Initial_Cleaning  a
where a.country_restricted_item =
(
select min(b.country_restricted_item) as country
from RT_Weekly_2000_Item_Master_Initial_Cleaning  b
where b.item_number = a.item_number
group by b.ITEM_NUMBER,b.ORG_ID
having count(b.item_number) > 1
)
0
 
LVL 2

Author Comment

by:mingfattt
ID: 13994427
It works perfectly, Thanx and you got the apple buddy

Wish Well

Me, Yee
0
 
LVL 11

Expert Comment

by:lluthien
ID: 13994433
glad to help :)

0
 
LVL 2

Author Comment

by:mingfattt
ID: 14007563
Sorry for troubling again, i found out that the last statement

having count(b.item_number) > 1

suppose to be this right

having count(b.COUNTRY_RESTRICTED_ITEM) > 1

just want to make sure... please help thanx

0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question