June 29, 2011 at 6:24 am
Dear all,
How to Select the first row from the table where
psp_item_no = DRR07DFAB0034
psp_ps_no = 16
?
June 29, 2011 at 6:34 am
select top 1 * from
where column=
and column=
order by desc
***The first step is always the hardest *******
June 29, 2011 at 6:36 am
glen.wass (6/29/2011)
select top 1 * from
where column=
and column=
order by desc
It may not be the 1st row. Ok.
June 29, 2011 at 6:47 am
TOP 1 isn't useful without an ORDER BY.
if you run this:
SELECT * FROM table
where psp_item_no = 'DRR07DFAB0034'
AND psp_ps_no = 16
how many rows show up? if there is more than one, which column, if you used an ORDER BY, would make that special row the *top* row?
Lowell
June 29, 2011 at 6:50 am
Lowell (6/29/2011)
TOP 1 isn't useful without an ORDER BY.if you run this:
SELECT * FROM table
where psp_item_no = 'DRR07DFAB0034'
AND psp_ps_no = 16
how many rows show up? if there is more than one, which column, if you used an ORDER BY, would make that special row the *top* row?
Please see the Attachment.. Your code is not working.
June 29, 2011 at 6:56 am
granted it may not be but by filterting down on the 2 collum you provide that bit of SQL will get out the top row of a returned set.
can you provide some representative data and table structure please
***The first step is always the hardest *******
June 29, 2011 at 6:57 am
what does not working mean?
you are showing something from excel that you pasted...that doesn't have anything to do with SQL errors or not working as expected or anything., though...
help us help you.
show us the CREATE TABLE (YorTableName... statement.
show us the actual SLq you tried...did you change it to the right tablename? it's pseudocode, of course, based on my best guess based on what you posted...you might ahve to put some effort into adapting it.
Lowell
June 29, 2011 at 6:58 am
subrata.bauri-1051938 (6/29/2011)
Lowell (6/29/2011)
TOP 1 isn't useful without an ORDER BY.if you run this:
SELECT * FROM table
where psp_item_no = 'DRR07DFAB0034'
AND psp_ps_no = 16
how many rows show up? if there is more than one, which column, if you used an ORDER BY, would make that special row the *top* row?
Please see the Attachment.. Your code is not working.
Please see the attachment
June 29, 2011 at 10:52 am
To get some real help you need to help us help you. Please see the link in my signature for best practices on posting questions. There are lots of people willing and able to help once we understand exactly what you are trying to do.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 29, 2011 at 11:16 pm
update #temp
set #temp.Quantity_mc = a.Total
from #temp Inner join
(
select psp_item_no,psp_item_var,sum(psp_qty_prpnl) as Total
from pmddb..pmd_mpsp_ps_postn pmd
WHERE NOT EXISTS (SELECT * From common..ims_variant_master ims,pmddb..pmd_mpsp_ps_postn pmd2
where description like 'Optional%' and
ims.stock_no= pmd2.psp_item_no and
pmd2.psp_ps_no = pmd.psp_ps_no and
pmd2.psp_io_flag = 'o'
)
group by psp_item_no,psp_item_var
) as a
on #temp.item_code = a.psp_item_no AND
#temp.variant_code=a.psp_item_var
This is my code where I want update my Temp Table.
Temp Table Definition :
create table #temp
(
item_code varchar(50) ,
variant_code varchar(10),
item_desc varchar(200),
uom varchar(10),
Quantity_mc numeric(28,3),
Available_Quantity numeric(28,3),
no_of_mechine numeric(28,3),
reminder numeric(28,3),
bom_item_code varchar(50),
bom_variant_code varchar(10),
bom_item_desc varchar(200),
bom_uom varchar(10),
)
The set of Result I have received by the code group by psp_item_no,psp_item_var is :
psp_ps_no psp_item_no psp_item_var psp_io_flag
7 DRR07DFAB0034 ## I
15 DRR07DFAB0034 ## I
But I only want the psp_ps_no =7 row, because this row has only relation with the Input psp_ps_no =16.
The relation I've to show in the Excel Sheet herewith :
June 30, 2011 at 8:20 am
I know we have some language barriers here but keep in mind that nobody can do anything with the code you loaded. You have an update to a temp table that uses a bunch of other tables. I am pretty sure that getting what you want is pretty simple but you have to put in some work. The create table statement is only a piece of the puzzle. You need to provide sample data to this table (insert statements). If there are other tables then you need provide ddl and sample data for them as well. Then you need to explain clearly what you want in your results (based on the sample data). Also, posting attached Excel documents will not get you your result. Many people, myself included, are reluctant to download office attachments unless we know the sender. If you are willing to put in the time to provide enough ddl and sample data so that somebody else can start working on your problem you will find people are willing to help. As I said before, please READ the article in my signature about how to gather the information required to make your request successful.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply