Database

4-Jul-09 3:27

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

Niladri_Biswas4-Jul-09 3:43

4-Jul-09 3:43

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
Smile | :)

Niladri Biswas

Elizma5-Jul-09 6:45

5-Jul-09 6:45

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

Isaac Gordon5-Jul-09 22:01

Isaac Gordon

5-Jul-09 22:01

I am Not getting your Question pls clearly define it.
use ranking Function like Dense_Rank() of SQL Server.

Dinesh

Elizma5-Jul-09 22:23

5-Jul-09 22:23

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

Enver Maroshi5-Jul-09 23:22

Enver Maroshi

5-Jul-09 23:22

Cross Apply is solution to your problem, with combination of XML, you can get a very nice output.

Elizma6-Jul-09 1:42

6-Jul-09 1:42

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!

Isaac Gordon5-Jul-09 23:59

Isaac Gordon

5-Jul-09 23:59

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

Elizma6-Jul-09 1:38

Transactional Replication Linked Sever error

6-Jul-09 1:38

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

Abdul Rahman Hamidy3-Jul-09 18:24

Abdul Rahman Hamidy

3-Jul-09 18:24

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

Re: Transactional Replication Linked Sever error

Niladri_Biswas4-Jul-09 3:51

Re: Transactional Replication Linked Sever error

4-Jul-09 3:51

Hi,

1. can i establish replication between these servers?

And: Yes.Check this

http://technet.microsoft.com/en-us/library/aa226171(SQL.70).aspx[^]

Search in GOOGLE with this statement "Sql Replication in distributed environment". You will get a lot of stuff.

2. can i establish linked server between these two servers.

Ans: Yes. Check this

http://msdn.microsoft.com/en-us/library/aa213778(SQL.80).aspx[^]

Hope this helps.
Smile | :)

Niladri Biswas

Jerry Hammond4-Jul-09 5:00

Jerry Hammond

4-Jul-09 5:00

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

How do you increment a temporary table?

DFlat4Now3-Jul-09 8:07

DFlat4Now

3-Jul-09 8:07

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

Re: How do you increment a temporary table?

Niladri_Biswas4-Jul-09 3:37

Re: How do you increment a temporary table?

4-Jul-09 3:37

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.
Smile | :)

Niladri Biswas

DFlat4Now4-Jul-09 5:55

DFlat4Now

4-Jul-09 5:55

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.

Re: How do you increment a temporary table?

Mycroft Holmes4-Jul-09 23:38

Mycroft Holmes

4-Jul-09 23:38

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

Online Database and Forum, where to start? Beginner*

rbwest863-Jul-09 5:44

rbwest86

3-Jul-09 5:44

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

Re: Online Database and Forum, where to start? Beginner*

David Mujica4-Jul-09 2:31

David Mujica

4-Jul-09 2:31

Check out DotNetNuke (DNN), it is an open-source package that allows you to build webstites with formus, galleries, all kinds of stuff.

Check it out here: http://www.dotnetnuke.com/[^]

Here is where you can find all kinds of "modules" that you can add to DNN for more functionality.
http://www.snowcovered.com/snowcovered2/[^]

Problem with SQL

Sider893-Jul-09 4:27

Sider89

3-Jul-09 4:27

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

Re: Problem with SQL

Blue_Boy3-Jul-09 4:55

Blue_Boy

3-Jul-09 4:55

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.

Re: Problem with SQL

Niladri_Biswas3-Jul-09 18:45