|
Hi Clever People.
I have a scenario and no matter what I try (other than cursors) I cant seem to get this working on an easy way.
I have StockCodes Say 10 Detail Rows per StockCode, but I want to see say the top 5 rows per StockCode.
Can anyone give me advice please on a quick and painless way or should I just go with the cursor?
Kind Regards
Elizma
|
|
|
|
|
Hope you have a table(tblStock) with a column like StockCode
StockCode
--------
1
2
3
4
5
6
7
8
9
10
The query is:
SELECT TOP 5 StockCode FROM TBLStockCode
Hope this helps
Niladri Biswas
|
|
|
|
|
Hi
Sorry. Thanx for your supply, maybe I didn't ask the question properly. I now the plain top 5 query. My problem is:
I have a master StockCode table that will contain unique StockCodes and their information.
I also have a Details Table that is linked to the Master StockCode Table. Each Stock Code in the Master might have 20+ rows in the Detail table. I want to specify the Top N PER StockCode.
E.g. If we use only TOP 2
StockCode1 - DetailDataRow1
DetailDataRow2
StockCode2 - DetailDataRow1
DetailDataRow2
Not maybe in this format, but I still need to get the TOP 2 rows per StockCode and not the First Top 2 Rows like what the normal solution that you suggested would do.
Hope this is a bit more clear.
Thanx for trying to help.
Elizma
|
|
|
|
|
I am Not getting your Question pls clearly define it.
use ranking Function like Dense_Rank() of SQL Server.
Dinesh
|
|
|
|
|
Hi
Thanx for your willingness to help, but I dont think there is an easy way to do what I was looking for and it is quite difficult to explain, so I decided to not waste too much time on a easy way out and to rather go for the cursor option to solve my problem.
I dont think your solution would have worked either since my problem was that I have 20+ Rows Reletad to 1 StockCode. I have 1000 StockCodes. So EACH StockCode Have 20 Related Rows. Giving the Total Number of Details to 20000.
So my question was. I only want to select the top 5 rows PER StockCode. I.O.W. I would now have 5000 Records returned to me. 5 Per StockCode.
Hope this makes more sense.
Kind Regards,
Elizma
|
|
|
|
|
Cross Apply is solution to your problem, with combination of XML, you can get a very nice output.
|
|
|
|
|
Hi
Thanx for your suggestion. It might have been able to work, but I forgot to say that this is on a SQL 2000 Server and unfortunately does not support XML!
|
|
|
|
|
Do you mean suppose i have a Table
stockcode item qty
1 101 10
1 102 20
1 103 20
2 104 25
2 105 20
2 106 25
2 107 26
with abc
as
(select Row_number() over (Partition by Stockcode order by stockcode) as [Row],StockCode,Item,Qty
from Stock)
select top 4 row,Item,Sum(qty) as [QTY] from abc group by Item,row
result
1 101 10
1 104 25
2 102 20
2 105 20
|
|
|
|
|
Hi
Also not quite what I needed. If I use your example:
stockcode item qty
1 101 10
1 102 20
1 103 20
2 104 25
2 105 20
2 106 25
2 107 26
The result I require is the following.
(Say I want the Top 2 PER StockCode)
Result:
StockCode Item Qty
1 101 10
1 102 20
2 104 25
2 105 20
Thanx for trying to help. I really do appreciate it.
Elizma
|
|
|
|
|
Dear All,
I have two Servers in two different locations which both runs Ms Sql 2005 Instance.
Server A with Ip address 72.32.16.32 which is public ip address located in London
and Server B with Ip address 192.168.2.3 which is locally configured and is a database server in Kabul.
Now i would like to establish transactional replication between these two servers.
1. can i establish replication between these servers?
2. can i establish linked server between these two servers.
any reference, idea would be really appreciated.
Abdul Rahaman Hamidy
Database Developer
Kabul, Afghanistan
|
|
|
|
|
|
Yes to both.
Check out Books Online for further details[^]
"My interest is in the future because I'm going to spend the rest of my life there." - Charles F. Kettering
|
|
|
|
|
Select *
Into #BC
From dfCert -- Creates master Certification table -> #BC
Declare @BoardCert Char(3)
Set @BoardCert = 'BCn'
Declare @Counter int
Set @Counter = 1
While @Counter < 10
Begin
Select
Biog_Nbr,
Max(Biog_Cert_ID) as aMaxBiogCertID,
Max(Cert_Year) as aMaxCertYear
Into
#tblBoardCert
From
#BC
Group By
Biog_Nbr
Delete From #BC
From #BC
Inner Join #tblBoardCert
On
#BC.Biog_Nbr = #tblBoardCert.Biog_Nbr
and
#BC.Biog_Cert_ID = #tblBoardCert.AMaxBiogCertID
and
#BC.Cert_Year = #tblBoardCert.aMaxCertYear
Where
#BC.Biog_Nbr = #tblBoardCert.Biog_Nbr
and
#BC.Biog_Cert_ID = #tblBoardCert.AMaxBiogCertID
and
#BC.Cert_Year = #tblBoardCert.aMaxCertYear
Set @BoardCert = Replace(Left(@BoardCert,2)+Cast(@Counter as Char),' ','')
Select *
Into #@BoardCert --<< Here's the problem. I need this to be #BC1 then BC2, etc up to BC9
From #tblBoardCert
Drop Table #tblBoardCert
Set @Counter = @Counter + 1
Print 'The counter has just increased to ' + Cast(@Counter as Char)
End
|
|
|
|
|
I guess there is some problem in the counter placement.
You are dropping the #TtblBoardCert and then you are incrementing.
Drop Table #tblBoardCert<br />
<br />
Set @Counter = @Counter + 1
So before the first increment itself the table is dropped.
Also I have a doubt in this line
Replace(Left(@BoardCert,2)+Cast(@Counter as Char),' ','')
Check if it is giving you the correct result or not.
Hope this helps.
Niladri Biswas
|
|
|
|
|
I drop the table because it needs to be rebuild on each cycle. The "Max" records are removed on the first cycle and then I pull the next set of "Max" records for the second cycle. This goes on until #BC is empty. Replace(Left(@BoardCert,2)+Cast(@Counter as Char),' ','') actually works. The real problem is the following:
Select *
Into #@BoardCert
From #tblBoardCert
It doesn't see #@BoardCert as #BC1 then #BC2 etc., it only sees it as #@BoardCert and so it errors because it thinks it's already written to #@BoardCert on the first cycle.
|
|
|
|
|
This just sounds weird, without going through the details of your script I think you can achieve this in 1 table by including a setID. If you need to create an incremented value over a set you can use row_number (over setID).
Creating and dropping temp tables is just bad design.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hello everyone,
I have no idea where to start with this so I will just explain my goal.
I want to create a website for users to share information and pictures with each other. I would like to provide a forum for users to communicate with each other, as well as allow the users create a database of items they own with pictures. So my question is in two parts.
Is there any free forum software that anyone would highly recommend?
How do I create a database online for users to share pictures and information, which can be viewed by all?
Thank you all very much for your efforts in pointing me in the right direction.
V/R
Rob
|
|
|
|
|
|
well, I am not well in sql want some help
so have dbo.Users and dbo.Customers tables, between them is 1:1 relationship
just watch them:Users Custimers
UserName I have done as unical from indexers.
when I select right this select * from Users,Customers where UserName='John' or Email='xxxx@yahoo.com' shows: That
and i want to show only UserName and it's corresponding Filds
how manage it?
and at last I don't want like this select * from Users,Customers where UserName='John' and Email='xxxx@yahoo.com'
C# Developer
|
|
|
|
|
Hope this will help you.
select customers.*,
(select top 1 users.username from users where users.username=customers.forename) as UsersTableUserName
from customers where username='John' and Email='xxxx@yahoo.com'
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Hope you are looking for this
SELECT U.USERNAME,C.*
FROM Customers C
INNER JOIN Users U
ON U.ID = C.ID
AND C.FORENAME = 'JOHN'
AND C.EMAIL = 'xxxx@yahoo.com'
Let me know in case of any concern.
Niladri Biswas
|
|
|
|
|
Hello All,
Here i'll explain very clear please read my problem with little patience.
I've two table X and Y. after every hour i'm moving all the records from X to y (now x table don't have any rows). After insert into Y table i'm checking 1 column value with the recent existed record in the Y table, If the value changes then i'm updating the Ischanged column value to 1 , if not with 0.
this is working fine for me.( I'm doing this in trigger)
Now the problem is , there are multiple records coming from X table with different column values in my desired column.
X Table :
Col1 Col2 Col3
1 1234 4781258
2 1234 4781258
3 1234 4781259
4 1234 4781258
5 1234 4781259
6 1234 4781258
7 1234 4781258
8 5487 5124873
9 5487 5124873
10 5487 5124873
Assume 1234 and 5478 records exist in Y table earlier
with coll 3 value 4781258 and 5124873 respectively.
Y Table (Expected)
Col1 Col2 Col3 IsChanged
1 1234 4781258 0
2 1234 4781258 0
3 1234 4781259 1 (changed from 4781258 to 4781259)
4 1234 4781258 1 (changed from 4781259 to 4781258)
5 1234 4781259 1 (changed from 4781258 to 4781259)
6 1234 4781259 0 (not changed with the prev record)
7 1234 4781258 1 (changed from 4781259 to 4781258)
8 5487 5124873 0 (not changed with the prev record)
9 5487 5124873 0
10 5487 5124873 0
Thanks in advance. If anybody knows the solution please help me out.
Avinash
|
|
|
|
|
You haven't actually asked a question.
You have explained your data. You have explained what you have done, and that it works.
For anyone to help you, you need to explain what you want to happen when you insert the records into Y, if different from what you are already doing. Or alternatively what to do with it once it is there.
What is your question?
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
here my expected output is not coming properly. tell me how to get my expected output
|
|
|
|
|
I am sorry, perhaps it is me, but I still do not fully understand your problem.
What are the rules for when a record from table X should update the record in table Y. Is it simply when Column 3 is different?
It would also help to see the code/SP or whatever, that you are currently using.
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
