Database

23-Aug-07 8:43

Use the function NEWID() to calculate a GUID for each row and sort by that.

SELECT TOP 5 *
FROM [MyTable]
ORDER BY NEWID()

-- modified at 14:54 Thursday 23rd August, 2007

Re: Random Select

PIEBALDconsult23-Aug-07 9:27

PIEBALDconsult

23-Aug-07 9:27

I do something similar; I have a table with two columns, one for IDs and one for random numbers.

The benefits are:

A) I don't have to add a column to the main (Employee) table
B) I can weight the selection process; that is, some records (employees) may have multiple entries
in the "hat", or none at all

The table is normally empty, but when it's time to select (Employee) records, I populate it, and make my selections (after an employee is selected I remove all the entries for that employee).
Then I clear the table again when selecting is complete.

One could also add a column to indicate that that ID has already been selected, and after selections delete the non-selected rows, leaving a table with the selected IDs which can then be used in a join.

Re: Random Select

Rocky#23-Aug-07 19:55

Rocky#

23-Aug-07 19:55

My idea is to get the max and min ID's from that Table.

Then use the Math.Random Function and give the limits in that range taken in previous step.

Select 5 Random ID's this way

Then select those records using this kindof construct in your where clause

ID IN(rand1,rand2,rand3,rand4,rand5)

Hope that's helpful for u

Rocky

You can't climb up a ladder with your hands in your pockets.

Re: Random Select

PIEBALDconsult24-Aug-07 7:57

PIEBALDconsult

24-Aug-07 7:57

That'll work; unless IDs have been removed or deactivated or something.
Maybe select ten random numbers in the range and use TOP 5.
Or loop until you have five.

Rocky# wrote:
You can't climb up a ladder with your hands in your pockets.

Sure I can, done it many times, but not on a steep ladder.

SQL query

Milind Panchal23-Aug-07 6:56

Milind Panchal

23-Aug-07 6:56

Hi,

I have a table with column named EmpID, EmpName, Salary,

I want to find out second highest salary.

Thanks Rose | [Rose]

Michael Potter23-Aug-07 8:20

23-Aug-07 8:20

SELECT MAX(Salary)
FROM [EmployeeTable]
WHERE Salary < (SELECT MAX(Salary) FROM [EmployeeTable])

Milind Panchal23-Aug-07 18:29

Milind Panchal

23-Aug-07 18:29

thanks for your help. Rose | [Rose]

Now I have a query that if I want find 3rd highest or 4th highest salary then how to write query?

thanks!

Rocky#23-Aug-07 20:11

Rocky#

23-Aug-07 20:11

select TOP 5 salary,empID
FROM EMP
Order by Salary DESC

Use this to find whatever number of top salaried personeel u want. Use a Cursor to fetch the records one by one. In this way u can ignore the records you dont want. Like for finding the 4th highest salary you can ignore the 1st 3 records and get the fourth one.

Rocky

You can't climb up a ladder with your hands in your pockets.

Michael Potter24-Aug-07 5:10

How to upload an Ntext column value to a text file

24-Aug-07 5:10

No need to use a cursor. Here is a query to get the fifth salary.

SELECT TOP 1 Salary, EmpId
FROM
(SELECT TOP 5 Salary, empID
 FROM EMP
 Order by Salary DESC) AS TopSalary
ORDER BY Salary ASC

edukulla23-Aug-07 6:48

edukulla

23-Aug-07 6:48

I have an ntext column in a table. I wanted this column value to be uploaded to a text file. I was using some code of my own, but it is not working out.Does anybody has any pointers on this? Any simple method to do this programmatically would be highly appreciated. I need to pull this code in one of my button click event on a windows page.

Thanks in Advance!

Santhosh Kumar Edukulla

How can I optimize this script?

Skanless23-Aug-07 6:11

Skanless

23-Aug-07 6:11

I am trying to create a reporting system which will give me the "Customer ID", "Number of Messages sent", "Is Message a part of a thread or Distinct", Message Status (i.e. New, Under Review, Cancelled or Resolved) within a given period. I honestly do not see no need for a curso in here but I believe one can be used but I do not understand the benefit of using one. The search is working fine except for the last "Select" prior to dropping the table is not providing Distinct User's. Users with multiple messages/Threads are being pulled up as many time as the amount of records found for them. It is the result set from the Last select that is displayed in the GUI.

Any assistance given will be appreciated.

SET NOCOUNT ON /* Don't return row counting to caller */

CREATE TABLE #MS_MessagesFromCustomer(
NumOfMessages BIGINT,
CustomerID VARCHAR(100),
Period datetime,
ThreadID int,
MessageStatus int

)

CREATE TABLE #MS_InquiriesByCustomer(
ThreadID int,
CustomerID VARCHAR(100),
TotalMessages int,
New int,
UnderReview int,
Cancelled int,
Resolved int,
TotalThreads int

)

Insert into #MS_MessagesFromCustomer(CustomerID, NumOfMessages, Period, ThreadID, MessageStatus)
Select MSM.SenderID, count(MSM.MessageID)Messages, Convert(varchar, MSM.SentDate, 101) [Sent Date],MSM.ThreadID,MST.CurrentStatusID [Current Status]
from dbo.MS_Messages MSM INNER JOIN dbo.MS_Threads MST ON MSM.ThreadID = MST.ThreadID
where MSM.SentDate >'3/1/2005'
and MSM.SentDate < '3/31/2005'
group by MSM.SenderID, MSM.SentDate, MSM.ThreadID, MST.CurrentStatusID
order by SenderID

--Select * from #MS_MessagesFromCustomer

INSERT INTO #MS_InquiriesByCustomer(ThreadID,CustomerID, TotalMessages, New, UnderReview, Cancelled,Resolved, TotalThreads)
Select Distinct(ThreadID)'ThreadID', CustomerID'Customer' , count(NumOfMessages) 'Total Messages Recevied',COUNT(CASE MessageStatus when 19 THEN MessageStatus end) AS 'New',
COUNT(CASE MessageStatus WHEN 20 THEN MessageStatus end) AS 'UnderReview',
COUNT(CASE MessageStatus WHEN 21 THEN MessageStatus end) AS 'Cancelled',
COUNT(CASE MessageStatus WHEN 22 THEN MessageStatus end) AS 'Resolved',
(COUNT(CASE MessageStatus WHEN 19 THEN MessageStatus end) +
COUNT(CASE MessageStatus WHEN 20 THEN MessageStatus end) +
COUNT(CASE MessageStatus WHEN 21 THEN MessageStatus end) +
COUNT(CASE MessageStatus WHEN 22 THEN MessageStatus end)) AS 'Total Threads'

from #MS_MessagesFromCustomer
group by customerID, ThreadID
order by customerID

SELECT Distinct(CustomerID),* FROM #MS_InquiriesByCustomer
Select * from #MS_MessagesFromCustomer

Select Distinct(CustomerID), TotalMessages 'Total Messages Recevied', New 'New', UnderReview 'Under Review', Cancelled 'Cancelled',Resolved 'Resolved', count(TotalThreads) 'Total Threads'
FROM #MS_InquiriesByCustomer
GROUP BY customerID,ThreadID, TotalThreads,TotalMessages, New, UnderReview, Cancelled, Resolved
ORDER BY CustomerID

DROP TABLE #MS_MessagesFromCustomer
DROP TABLE #MS_InquiriesByCustomer

Skan

If you knew it would not compile why didn't you tell me?!?!?!

Re: How can I optimize this script?

Michael Potter23-Aug-07 8:41

return int (urgent) [modified]

23-Aug-07 8:41

DISTINCT used as an argument of a SELECT clause does not eliminate rows due to a single duplicate column. It eliminates rows when all columns are equivalent. The whole row would have to be the same for it to begin its removal process.

DISTINCT can be used as an argument in an aggregate like AVG(DISTINCT price). Here it will eliminate duplicate prices before calculating the average.

I think you need to re-examine how you have coded your store procedure. It seems a little over done. Not sure what your desired end result is but, I would think a much simpler LEFT JOIN would handle most of the work.

ksaw12323-Aug-07 5:28

ksaw123

23-Aug-07 5:28

Hellow everybody

I want a query that will return number of rows in the database , but it will take three parametes from the calling method.

I implemented a query which is as follows:
Select Count(*)
From MainRecord
Where (MealType = @MealType) AND (ID = @ID) AND ( CAST(FLOOR(CAST(DATE AS FLOAT)) AS DATETIME = @DATE)

The purpose of this query is that I want to count number of rows if the ID, MealType and DATE are in the database. Also, the @DATE I am giving in the calling query is now date which is for example ( today date [08/23/2007 00:00:00]). I wanted to be checked with the date that is stored in the database where the DATE that is stored in the database is DATETIME which is in this form (08/22/2007 13:14:50). Therefore, the CAST(FLOOR(CAST(DATE AS FLOOT)) AS DATETIME will convert that datetime to (08/22/2007 00:00:00) so we could compare then just in the date.

I don’t know if this way is right or not and if not how can I do it.

Thaanks,

-- modified at 11:36 Thursday 23rd August, 2007

Re: return int (urgent)

Colin Angus Mackay23-Aug-07 5:34

Re: return int (urgent) [modified]

23-Aug-07 5:34

CanadianBoy wrote:
FLOOT

Did you mean FLOAT?

CanadianBoy wrote:
I don’t if this way is right or not and if not how can I do it.

Have you tried experimenting? Building up a part of the query to see if it does what you want?

SELECT GETDATE()
SELECT CAST(GETDATE() AS FLOAT)
SELECT FLOOR(CAST(GETDATE() AS FLOAT))
SELECT CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)

Looks like it works to me.

-- Always write code as if the maintenance programmer were an axe murderer who knows where you live.

Upcoming FREE developer events:
* Glasgow: Agile in the Enterprise Vs. ISVs, Mock Objects, SQL Server CLR Integration, Reporting Services, db4o ...
* Reading: SQL Bits

My website

ksaw12323-Aug-07 5:38

ksaw123

23-Aug-07 5:38

Yes, I mean foat. Does this way logical and it will work properly. If there is an other way i can try especially to check the time.

-- modified at 11:53 Thursday 23rd August, 2007

Re: return int (urgent)

Xandip24-Aug-07 17:31

Xandip

24-Aug-07 17:31

There is another way to check the date.. try this..

Select * From ....
Where
Convert(DateTime, @ParameterDate, 103) = Convert(DateTime, GetDate(), 103)

This is for checking with current date(GetDate()). For more precision yo can add the following lines of code with the above code.

And Year(@ParameterDate) = Year(GetDate())
And Month(@ParameterDate) = Month(GetDate())

The name is Sandeep

Re: return int (urgent)

ksaw12325-Aug-07 0:14

ksaw123

25-Aug-07 0:14

Thanks a lot for your reply both of you.

loop in sql table

costavo23-Aug-07 4:06

costavo

23-Aug-07 4:06

hi all,
i have to loop in a table in sql database to find a certain record and compare it or what ever,....
the question is what steps i have to do to loop in a table
using c# lang in a win app.
regards

Re: loop in sql table

Colin Angus Mackay23-Aug-07 5:30

23-Aug-07 5:30

costavo wrote:
or what ever

costavo wrote:
i have to loop in a table in sql database to find a certain record and compare it or what ever,....
the question is what steps i have to do to loop in a table
using c# lang in a win app.

I presume you are talking about a DataTable rather than one in SQL Server.

foreach(DataRow row in myDataTable.Rows)
{
    // Do what you need to here.
}

Connect to sql server

messages23-Aug-07 3:56

messages

23-Aug-07 3:56

Hi everyone,
I was installing sql server 2005 and it installs perfect with all components without any errors now I run SQL Server Management Studio but it wants a name to server but I dont know name of server how do I solve this problem?
Thanks

WhiteGirl2323-Aug-07 4:00

WhiteGirl23

23-Aug-07 4:00

If you use Server Authentification use the name of your computer ,the user "sa" and the password that you set to sql server during the installation.

messages23-Aug-07 4:14

messages

23-Aug-07 4:14

Im using of:
Server type: Database Engine
Server name: "I dont know what is it"
Authentication: Im trying with "Windows Authentication" and also Sql Server Authentication with sa" but I got this error:
Im trying with your answer(sa) but I got this error:
TITLE: Connect to Server
------------------------------

Cannot connect to local.

------------------------------
ADDITIONAL INFORMATION:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 1231)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=1231&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

Colin Angus Mackay23-Aug-07 4:03

23-Aug-07 4:03

It is the name of the machine, or "(local)" for the server on the machine you are using. If you installed a named instance (something you would have to do explicitly) then you will have to add /InstanceName after the machine name.

messages23-Aug-07 4:17

messages

23-Aug-07 4:17

Im using of:
Server type: Database Engine
Server name: "I dont know what is it" and also I checked with local
Authentication: Im trying with "Windows Authentication" and also Sql Server Authentication with sa" but I got this error:

TITLE: Connect to Server
------------------------------

Cannot connect to local.

------------------------------
ADDITIONAL INFORMATION:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 1231)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=1231&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

Colin Angus Mackay23-Aug-07 5:35