Database Discussion Boards

First Prev Next

OrderBy Dilema

danyDude14-Feb-10 11:53

danyDude

14-Feb-10 11:53

Hy,
I have something like this:

SELECT Code, SUM(QualityData.NrOfDefects) AS Defects
FROM QualityData
GROUP BY Code
ORDER BY SUM(QualityData.NrOfDefects);

The dilema is that

ORDER BY Defects

does not work

and

ORDER BY SUM(QualityData.NrOfDefects);//causes a second SUM??

Is there a better way?
Thanks

Re: OrderBy Dilema

loyal ginger14-Feb-10 14:43

loyal ginger

14-Feb-10 14:43

SELECT Code, Defects from
(SELECT Code, SUM(QualityData.NrOfDefects) AS Defects
FROM QualityData
GROUP BY Code)
ORDER BY Defects;

Re: OrderBy Dilema

danyDude14-Feb-10 15:46

danyDude

14-Feb-10 15:46

thanks

Re: OrderBy Dilema

loyal ginger16-Feb-10 3:50

loyal ginger

16-Feb-10 3:50

OK. I did not have access to much resources when I answered your question. I assumed that you had problems with your original queries. However, here I have access to my environment, and I tested your query

SELECT Code, SUM(QualityData.NrOfDefects) AS Defects
FROM QualityData
GROUP BY Code
ORDER BY SUM(QualityData.NrOfDefects);

It works with no problem. It works with no problem if I use

ORDER BY Defects

So just ignore my "solution". There must be something else in your problem.
My test environment is MySQL version 5.

Re: OrderBy Dilema

danyDude17-Feb-10 20:56

danyDude

17-Feb-10 20:56

It's all fixed now.

I now know for a fact(tested) that even the

ORDER BY SUM(QualityData.NrOfDefects);

does not cause a recalculation.

Re: OrderBy Dilema

i.j.russell14-Feb-10 22:11

i.j.russell

14-Feb-10 22:11

Your initial query is fine; it will not require a second set of calculations for the sort. To verify this, run the query in Management Studio and look at the Actual Execution Plan.

Music Database??

AmbiguousName12-Feb-10 23:35

AmbiguousName

12-Feb-10 23:35

Hello guys.....title says it all. How can we make a database of music and access it using vb.net or C#.net?? Confused | :confused:

Re: Music Database??

Andy_L_J13-Feb-10 0:10

Andy_L_J

13-Feb-10 0:10

Buy a Database book or copy one of these:

Google[^]

I don't speak Idiot - please talk slowly and clearly

'This space for rent'

Driven to the arms of Heineken by the wife

Re: Music Database??

AmbiguousName13-Feb-10 1:05

AmbiguousName

13-Feb-10 1:05

This is not what I was talking about. Lets suppose that i wanna make my own media player. In this case, I will need to access the music from my own hard disk and make a playlist.....

Re: Music Database??

Richard MacCutchan13-Feb-10 2:20

Richard MacCutchan

13-Feb-10 2:20

Muzammil Saeed wrote:
This is not what I was talking about.

Your title says "Music Database", and your first entry states "Hello guys.....title says it all.", so which is it to be? If you have no interest in creating a music database then try and make your question clearer.

To make a playlist you need to scan all your music files, extracting the title, composer, orchestra etc information and building some sort of database which your media player can access. If you think a database is too much work you can use any alternative file type that you feel comfortable with.

txtspeak is the realm of 9 year old children, not developers. Christian Graus

Re: Music Database?? [modified]

Andy_L_J13-Feb-10 9:34

Andy_L_J

13-Feb-10 9:34

You did not talk about anything - you were hoping that someone would give you their work without you lifting a finger.

This is an easily found site that will do half of the work for you, if you can find the library you need to work from:

Database Models[^]

Edit you can also follow the instructions here: MSDN[^]

I don't speak Idiot - please talk slowly and clearly

'This space for rent'

Driven to the arms of Heineken by the wife

modified on Saturday, February 13, 2010 3:53 PM

Re: Music Database??

thatraja13-Feb-10 17:40

thatraja

13-Feb-10 17:40

It's an Useful site Andy. Thank you. Rose | [Rose]

thatraja

Tips/Tricks|Brainbench certifications

Re: Music Database??

AmbiguousName13-Feb-10 20:41

AmbiguousName

13-Feb-10 20:41

Thanx for sharing info about MSDN link....but i dont know why poeple over-react. I did not meant that someone will give his knowledge to me,without my finger raised ....and stuff like that you wrote. If person A asks person B...it already means that person B might have more knowledge than person A, and showing respect to person B. Can I do anything to your knowledge except asking you? The only option remains is not to ask from you but from someone else.

Re: Music Database??

Andy_L_J13-Feb-10 20:50

Andy_L_J

13-Feb-10 20:50

It is good form to show what you have tried, or even describe the solution that you "think" may be along the right lines. Unfortunately, you did neither. Therefore it is difficult for anyone to answer your question - we are not mind readers. (My first reply to you was to useless information because that is what I had to go on.)

If you had said in the op that you had looked at various sites and MSDN then the response would have been warmer.

It did appear however that you did neither.

I don't speak Idiot - please talk slowly and clearly

'This space for rent'

Driven to the arms of Heineken by the wife

Re: Music Database??

dan!sh 13-Feb-10 2:32

dan!sh

13-Feb-10 2:32

Which database are you speaking of?

I can think of two options:

1. Saving the path of the music files in the database and then loading the files in the player from that path.
2. Saving the file as a Blob in the database and reading and playing them through your app.

"No matter how many fish in the sea; it will be so empty without me." - From song "Without me" by Eminem

search on xml field in table of sql server?

Tridip Bhattacharjee11-Feb-10 21:38

Tridip Bhattacharjee

11-Feb-10 21:38

suppose i have few fileds in table and one filed is xml type. how to search this.

the xml which is stored in my xml filed like

<OpenShipments xmlns="x-schema:C:\UPSLabel\OpenShipments.xdr">
<OpenShipment ShipmentOption="RS" ProcessStatus="">
<ShipTo>
<CompanyOrName>CRESCENT AUTO SERVICES LTD</CompanyOrName>
<Attention>ZULFI</Attention>
<Address1>16-18 THE CRESCENT</Address1>
<Address2 />
<Address3>SOUTHALL</Address3>
<CountryTerritory>GB</CountryTerritory>
<PostalCode>UB1 1BE</PostalCode>
<CityOrTown>SOUTHALL</CityOrTown>
<StateProvinceCounty>SOUTHALL</StateProvinceCounty>
<Telephone>02085719998/02085742845</Telephone>
</ShipTo>
<ShipmentInformation>
<ServiceType>UPS Standard</ServiceType>
<PackageType />
<NumberOfPackages>1</NumberOfPackages>
<ShipmentActualWeight>5</ShipmentActualWeight>
<DescriptionOfGoods>Car Parts</DescriptionOfGoods>
<BillingOption>PP</BillingOption>
<ReturnService>
<Options>PRL</Options>
<MerchandiseDescOfPackage>OLD CORE RETURN</MerchandiseDescOfPackage>
</ReturnService>
</ShipmentInformation>
</OpenShipment>
</OpenShipments>

we can query sql server data very easily but i dont know how to query the xml data which stored in xml filed.
how to query address field in my xml or how to query Options field,how query CompanyOrName.

please help me to construct the sql. guide me in detail with few sample for querying xml datatype.
thanks in advance

tbhattacharjee

Re: search on xml field in table of sql server?

Pranay Rana12-Feb-10 1:35

Pranay Rana

12-Feb-10 1:35

Try this

http://www.15seconds.com/Issue/050803.htm[^]

Re: search on xml field in table of sql server?

Eddy Vluggen12-Feb-10 1:37

Eddy Vluggen

12-Feb-10 1:37

Tridip Bhattacharjee wrote:
how to query address field in my xml or how to query Options field,how query CompanyOrName.

A XML-query will look somewhat more complicated then a "regular" query. I hope that the example below provides a good impression of the used construct;

SQL

DECLARE @ShipXML XML
    SET @ShipXML = 
	'<OpenShipments>
		<OpenShipment>
			<NumberOfPackages>1</NumberOfPackages>
			<ShipmentActualWeight>5</ShipmentActualWeight>
			<DescriptionOfGoods>Car Parts</DescriptionOfGoods>
		</OpenShipment>
		<OpenShipment>
			<NumberOfPackages>2</NumberOfPackages>
			<ShipmentActualWeight>3</ShipmentActualWeight>
			<DescriptionOfGoods>Bike Parts</DescriptionOfGoods>
		</OpenShipment>
	</OpenShipments>'

SELECT shs.sh.value('NumberOfPackages[1]','bigint') AS NumberOfPackages
      ,shs.sh.value('ShipmentActualWeight[1]','bigint') AS ShipmentActualWeight
      ,shs.sh.value('DescriptionOfGoods[1]','varchar(20)') AS DescriptionOfGoods
  FROM @ShipXML.nodes('OpenShipments/OpenShipment') shs(sh)
 WHERE shs.sh.exist('.[DescriptionOfGoods != "Car Parts"]') = 1

The example was built using sample-code from MSDN[^].

I are Troll Suspicious | :suss:

Best Approach - Crystal To Excel

bdaoust11-Feb-10 12:20

bdaoust

11-Feb-10 12:20

We currently have a Crystal Report that uses a SQL 2005 database as it's datasource.

Both start date and end date are the only two parameters that are sent to stored procedure. These dates can vary - meaning it's not always MTD or YTD.

The user would like the report details to be exported to Excel.

I've used DTS in the past, but haven't had an opportunity to play with SSRS or SSIS.

Which should I focus on for this task?

We do have a Sharepoint portal and ideally, the process can start there by clicking a link.

How would the parameters be inputted by the user?

Thanks in advance.

Re: Best Approach - Crystal To Excel

Mycroft Holmes11-Feb-10 15:15

Mycroft Holmes

11-Feb-10 15:15

One of the (minor) reasons we dumped (on) Crystal was the export to excel was so crappy.

We use SSRS and the excel export is one hell of a lot better than Crystal used to be. Having said that SSIS does not support data dumps, it shouldn't it is a REPORTING tool. I repeat this line to my management weekly, it does not help. They still insist that data dumps are a requirement.

So one of our devs has extended SSRS to export csv files via RS. This is not possible in CR and is pretty ugly even in SSRS, it means hacking the UI to a custom page that calls a stored proc that writes the file to a folder and then displays a link for the user to download the file. As I said ugly, but it works without having to pull major volume to the UI to export.

Never underestimate the power of human stupidity
RAH

Error establishing a relation in access database through sql statement

A&Ms11-Feb-10 9:19

A&Ms

11-Feb-10 9:19

Hi I Execute the following sql statement successfully for a Access Database.

CREATE TABLE Scores ( 
	StudentCode Text(9) NOT NULL,
	CourseCode Text(7) NOT NULL,
	SemesterCode Text(3) NOT NULL,
	Score Double
);

CREATE TABLE Courses ( 
	CourseCode Text(7) NOT NULL,
	CourseName Memo NOT NULL,
	CourseTypeID Integer NOT NULL,
	CourseCategoryID Integer NOT NULL
);

ALTER TABLE Scores ADD CONSTRAINT PK_Grades 
	PRIMARY KEY (StudentCode, CourseCode, SemesterCode);

ALTER TABLE Courses ADD CONSTRAINT PK_Courses 
	PRIMARY KEY (CourseCode);

ALTER TABLE Courses
	ADD CONSTRAINT UQ_Courses_CourseCode UNIQUE (CourseCode);

But Finally I Get Error message(Syntax error in CONSTRAINT clause.) on this satement that is at the end of my execution list.

ALTER TABLE Scores ADD CONSTRAINT FK_Scores_Courses 
	FOREIGN KEY (CourseCode) REFERENCES Courses (CourseCode)
ON UPDATE CASCADE;

And I don't know Why?
Please Help me on how to establish a relation in access database through sql statement?

MsAccess Query problem

vishnukamath10-Feb-10 20:38

vishnukamath

10-Feb-10 20:38

hi,

i wrote msAccess query like.

SELECT T1.[CID],T2.[Name]
FROM T1 INNER JOIN T2 ON T1.TID =T2.TID
WHERE T1.CID in ('select CID from T3')

here i'm getting error :

"Cannot join on memo,ole,or hyperlink object (T1.TID =T2.TID )"

please send solution for this

Thanks & Regards,
Vishnu.

Re: MsAccess Query problem

Mycroft Holmes10-Feb-10 23:36

Mycroft Holmes

10-Feb-10 23:36

So one of your fields is in this data format, check each field in the join and change the data type of the offending field.

Never underestimate the power of human stupidity
RAH

Problem with Distinct and Order By when querying csv with .NET System::Data::OleDb::OleDbConnection [modified]

T21029-Feb-10 22:40

T2102

9-Feb-10 22:40

I need to query a csv file with C++ .NET and get a distinct list which is ordered by multiple criterion. If I write regular SQL, then i get the error "order by clause conflicts with distinct". If I use the prefix "SELECT * FROM ( SELECT" and suffix ")", then the order is lost.

My current workaround is to manually modify the results to a distinct set using C++.

modified on Wednesday, February 10, 2010 5:24 AM

Re: Problem with Distinct and Order By when querying csv with .NET System::Data::OleDb::OleDbConnection

dan!sh 10-Feb-10 2:57

dan!sh

10-Feb-10 2:57

What is the exact query you are using?

"No matter how many fish in the sea; it will be so empty without me." - From song "Without me" by Eminem

Last Visit: 31-Dec-99 18:00 Last Update: 17-May-24 22:57

Refresh

ᐊ Prev 1...669 670 671 672673674 675 676 677 678 Next ᐅ

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.