couldn't you have done a DISTINCT [CATEGORY] in the select?

10/23/2009 5:12:06 PM

Like how?

10/24/2009 6:30:45 AM

SELECT DISTINCT [FOOD].[CATEGORY],[FOOD].* FROM [FOOD]
LEFT OUTER JOIN [FOOD] As [CHEAPEST]
ON [CHEAPEST].[CATEGORY]=[FOOD].[CATEGORY] AND [CHEAPEST].[COST] < [FOOD].[COST]
WHERE [CHEAPEST].[COST] IS NULL

I haven't set this up to test, so it may not work at all or not as intended, it's just one of the first things I would have tried

10/24/2009 7:50:02 AM

All i can think of is

SELECT *
FROM food
WHERE NAME IN (SELECT MIN (NAME) AS NAME
FROM food
GROUP BY CATEGORY)

10/24/2009 10:17:42 AM

ignore above query....i dint read the cost part correctly



[Edited on October 24, 2009 at 12:02 PM. Reason : fuck]

10/24/2009 11:46:33 AM

I need to select the value in a table that determines the top 20%. So I need to select the min number from the top X results on a table, sorted by that number.

SET ROWCOUNT = percentile

SELECT min(number) FROM table
ORDER BY number


doesnt work, because I'm not grouping by anything. I can't group because then my percentile will be off. Any thoughts?

11/11/2009 1:13:53 PM

Do you mean you only want to display the first 20% of all the result rows?

SELECT TOP 20 PERCENT * FROM table
ORDER BY number

[Edited on November 11, 2009 at 1:26 PM. Reason : .]

11/11/2009 1:25:06 PM

example would be nice

11/11/2009 1:32:42 PM

^,^^. Thanks for the help. Sorry I wasn't clear. An example:

COLUMN
56
34
23
22
20
16
13
12
11
6


I wanted a query that returned 23 if I asked for 20%, or 16 if I asked for 50% etc. I managed it with temp tables. Slow though

11/17/2009 4:36:42 PM

So you only want it to return the first row starting at the given percentage of the full results?

SELECT TOP 1 *
FROM (SELECT TOP 20 PERCENT * FROM table ORDER BY column) ORDER BY column DESC

Obviously for this to work it has to be able to invert the initial sort so it grabs the last result of the top 20 percent.


[Edited on November 17, 2009 at 5:00 PM. Reason : .]

11/17/2009 4:53:21 PM

pretty cool script for taking a look at all your tables and see who's taking up the most room, among other things...look at the first comment, not the blog entry.

http://www.keithelder.net/blog/archive/2009/11/25/how-to-get-table-sizes-in-sql-server.aspx

i tried to post it here but tww wasn't havin' it.

12/8/2009 12:48:15 PM

Error Code : 1142
SELECT command denied to user 'user123'@'204.103.201.122' for table 'tables'

12/8/2009 1:32:44 PM

i usually use

CREATE TABLE #temp (
table_name sysname ,
row_count INT,
reserved_size VARCHAR(50),
data_size VARCHAR(50),
index_size VARCHAR(50),
unused_size VARCHAR(50))
SET NOCOUNT ON
INSERT #temp
EXEC sp_msforeachtable 'sp_spaceused ''?'''
SELECT a.table_name,
a.row_count,
COUNT(*) AS col_count,
a.data_size
FROM #temp a
INNER JOIN information_schema.columns b
ON a.table_name collate database_default
= b.table_name collate database_default
GROUP BY a.table_name, a.row_count, a.data_size
ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC
DROP TABLE #temp

from http://blog.sqlauthority.com/2007/01/10/sql-server-query-to-find-number-rows-columns-bytesize-for-each-table-in-the-current-database-find-biggest-table-in-database/

[Edited on December 8, 2009 at 1:43 PM. Reason : variations in the comments as well]

12/8/2009 1:41:00 PM

Not a query question but -

Are there any simple 1-2 step ways to import data? I know its only like 5 steps to import as a task in management studio, but for tiny sets of data that I just need to do a quick tests on and then forget about, this gets annoying. Something along the lines of selecting a database and pasting your data.

12/31/2009 12:09:02 PM

Are the little sets of data in the same format, or are they all different?

12/31/2009 12:18:31 PM

i've used this before http://vyaskn.tripod.com/code.htm#inserts to generate insert statements

[Edited on December 31, 2009 at 12:22 PM. Reason : or you could write ad-hoc queries that generate inserts]

12/31/2009 12:21:54 PM

php?

Link tables in Access and cut & paste there?

12/31/2009 2:47:23 PM

^ That would work (the access thing), but it might be more trouble than its worth, ill check it out.

^^ Thanks for the link, Ill read through it.

12/31/2009 3:40:35 PM

mySQLyog has a cool import feature

1/4/2010 4:11:13 PM

Ok, i'm stumped.

The following query takes longitude and latitude of your zip code and computes a number known as 'distance'. If you take the square root of this number it gives you rough mileage of how far away you are from the current zip you entered in. It currently works fine and I wanted to limit based on distance instead of just giving you a huge list.

SELECT
  dCity,
  dState,
  dAddress,
  dContact,
  dDealership,
  dPhone,
  dFax,
  dEmail,
  dZIP,
  (POW((69.1*(z.lon-77.91990)*COS(35.71530/57.3)),"2")+POW((69.1*(z.lat-35.71530)),2)) AS distance,
  dWebLink,
  dAccountType
FROM dealers d
  INNER JOIN zipdata z
    ON (z.zipcode = d.dZIP)
WHERE 1
    AND dCountryID = 'US'
 ORDER BY distance

3/16/2010 4:01:00 PM

just use

AND (POW((69.1*(z.lon-77.91990)*COS(35.71530/57.3)),"2")+POW((69.1*(z.lat-35.71530)),2)) <= 23500.


It doesn't know the column names when its selecting

3/16/2010 4:28:59 PM

^ yes, that works, sorry.

I did it another way as well->

Just created it as a view.
Then ran another query to pull the data from it.



[Edited on March 16, 2010 at 4:55 PM. Reason : g]

3/16/2010 4:53:21 PM

^hmm, dunno why it gave zeros.

view is probably better though, then you don't have to give them access to your tables.

3/16/2010 4:56:04 PM

Looking for help with DROP LOGIN or sp_droplogin

The problem is the login contains the computer name. E.g.

DESKTOP001\SQLUser

So a call that would work is:
drop login [DESKTOP001\SQLUser]

I want to make the solution generic, so it can apply to multiple computers. I've tried building a variable (varchar) such that:

@login is the string '[DESKTOP001\SQLUser]'

EDIT:

Answered my own question.

Had to change it to sp_revokelogin and had to add 'exec' in front, otherwise it didn't work.
and then
drop login @login

Looks like that call doesn't support that level of shenanigans. Any ideas?

[Edited on March 18, 2010 at 3:34 PM. Reason : -]

3/18/2010 3:17:40 PM

not sure what you're asking.
What are you executing, and what is the response?

make sure you have permission to ALTER ANY LOGIN

3/18/2010 8:02:35 PM

Oops. Looks like I dropped that edit in the middle of the original post instead of the end. I wanted a command that would remove a <domain\login> that could be executed on multiple domains without the need to be edited for each domain.

Eg.

I couldn't just say sp_revokelogin [myDomain001\MyUser] because it wouldn't work (without edits) on myDomain002.

I was using a variable to build the query, but had trouble because "sp_revokelogin @variable" didn't work. It only worked once I put 'exec' out in front. "exec sp_revokelogin @variable".

3/19/2010 8:11:59 AM

for robster

Quote :

" No generic mysql threads were still active ... so here goes. Need help with a mysql script: Have a table with many rows, where column 'group' may be the same for some rows. I want to pull out a result list showing just the list of 'group' values that are duplicated in the table. So: Select group FROM users group ------- groupA groupB groupC groupB groupB groupC groupD If this was the full list of users, then I want to just get results showing the following, because groupB and groupC are the only 'group' values that exist in more than one row. group ------ groupB groupC Anyone know an efficient way of doing this? Alternately, result could be a list of all the distinct 'group' values with a column that gives a count of the number of total rows in the db which have that same group value. Then, I could just filter out those whos count is only 1. Thanks for any help! "

i dunno how this falls as far as efficiency goes, but i would do it like this

select u.group, count(*)
from users u
group by u.group
having count(*) > 1
order by count(*) desc

5/8/2010 7:06:39 AM

oh snap, I just took all my tests on this. Hope I did well

5/8/2010 9:16:25 AM

thanks q ... works great

5/8/2010 8:43:14 PM

this is more of php issue than a sql issue i think...

I've got my sql query pulling a list of data and i've got my php page doing a foreach loop and generating my content. It generates the dealer name along with their status. Their current status will show up as selected and other possible statuses are listed. Everything is good.

I would like to cycle through each account and update the sql entry if their statuses have changed.

I assume i'll have to make another foreach loop but how do I determine which row to update if they all have the same name accounttype? i guess i'll have to pull the ID number for each dealer.

<SELECT NAME="accounttype">
    <option Value="Application Received">Application Received</option>
    <option value='Application Received'>Application Received</option>
            <option value='Sales Manager Approved'>Sales Manager Approved</option>
            <option value='Additional Information Required'>Additional Information Required</option>

            <option value='Credit Approved'>Credit Approved</option>
            <option value='Credit Declined'>Credit Declined</option>
            <option value='Floorplan Sent To Dealer'>Floorplan Sent To Dealer</option>
            <option value='Signed Documents Received'>Signed Documents Received</option>
            <option value='Signed Documents Incomplete'>Signed Documents Incomplete</option>
            <option value='Dealer Activated'>Dealer Activated</option>

    </SELECT>
    </td>
    </tr>
        <td><strong>Dealer:</strong> dasdada</td>
    <td height="26" align="right"><strong>Status:</strong></td>   
    <td>
    <SELECT NAME="accounttype">

    <option Value="Application Received">Application Received</option>
    <option value='Application Received'>Application Received</option>
            <option value='Sales Manager Approved'>Sales Manager Approved</option>
            <option value='Additional Information Required'>Additional Information Required</option>
            <option value='Credit Approved'>Credit Approved</option>
            <option value='Credit Declined'>Credit Declined</option>

            <option value='Floorplan Sent To Dealer'>Floorplan Sent To Dealer</option>
            <option value='Signed Documents Received'>Signed Documents Received</option>
            <option value='Signed Documents Incomplete'>Signed Documents Incomplete</option>
            <option value='Dealer Activated'>Dealer Activated</option>
    </SELECT>
    </td>

continued on..

5/20/2010 10:41:31 AM

<SELECT name="accounttype[$dealerID]">

5/20/2010 11:08:03 AM

qntmfred - Do you know how using 'having' compares with rank over partiton?

I would have done something like this, just because ive never seen 'having' before.

select [group] from
(
select u.[group], RANK() over (PARTITION by u.[group] order by [another column] desc) as Rnk
from users u
) tmp
where Rnk=2


I learn something new about SQL every day...

5/20/2010 11:40:29 AM

^^ yep. Got it working.

5/20/2010 11:51:16 AM

^^ i'm actually not sure how it compares. i wasn't familiar with rank/partition

5/20/2010 12:08:03 PM

^ Looks like they have pretty similar execution plans, I think yours is slightly more efficient

5/20/2010 12:49:25 PM

another technique that i learned recently that i was shocked to have just discovered is common table expressions

WITH 
	Sales_CTE AS
	(
		SELECT SalesPersonID, COUNT(*), MAX(OrderDate)
		FROM Sales.SalesOrderHeader
		GROUP BY SalesPersonID
	)

SELECT E.EmployeeID, OS.NumberOfOrders, OS.MaxDate, E.ManagerID, OM.NumberOfOrders, OM.MaxDate
FROM HumanResources.Employee AS E
JOIN Sales_CTE AS OS ON E.EmployeeID = OS.SalesPersonID
LEFT OUTER JOIN Sales_CTE AS OM ON E.ManagerID = OM.SalesPersonID
ORDER BY E.EmployeeID;

WITH 
	cteA AS
	(
		SELECT col1, col2
		FROM tableA
	),
	cteB AS
	(
		SELECT col3, col5
		FROM tableB
	)

SELECT *
FROM cteA
LEFT JOIN cteB
ON cteA.col1=cteB.col3

5/20/2010 2:34:31 PM

^ Yeah, just got wind of them about 2 months ago. I think SQL is a language that you only need a few tricks to do almost everything, but there are a lot more efficient ways to do things if you investigate.


A great example are pivot and unpivot. You can do similar things with ugly temp tables, but pivot handles them beautifully.

cursors are another one

5/20/2010 4:13:04 PM

<- not a fan of cursors

[Edited on May 20, 2010 at 4:43 PM. Reason : or triggers. or hell, i don't even like stored procedures that much ]

5/20/2010 4:42:44 PM

bump

12/7/2010 2:20:56 PM

Thank you for bumping this thread! I have yet another question

Let's say I have 3 tables: user, type, and relationship. Their data looks something like this:

user

+----+------+
| id | name |
+----+------+
|  1 | John |
|  2 | Mary |
|  3 | Will |
|  4 | Anna |
+----+------+

+----+-------+-----------+
| id | level |   type   |
+----+-------+-----------+
|  1 |   3   | volunteer |
|  2 |   1   |  manager  |
|  3 |   2   |  trainer  |
+----+-------+-----------+

+---------+---------+
| user_id | type_id |
+---------+---------+
|    1    |    1    |
|    4    |    1    |
|    2    |    1    |
|    1    |    2    |
|    3    |    2    |
|    2    |    3    |
|    1    |    3    |
+---------+---------+

12/7/2010 3:01:26 PM

select 
	(select uu.name from [user] uu where uu.id=u.id)
	, (select tt.type from type tt where tt.level = MIN(t.level))
from [user] u
join relationship r on r.user_id = u.id
join type t on t.id = r.type_id
group by u.id

12/7/2010 3:15:13 PM

Hmmm. MySQL gives me this error when I try that:

Quote :

"#1242 - Subquery returns more than 1 row"

It points to the select tt.type... query as the problem.

[Edited on December 7, 2010 at 3:47 PM. Reason : Error code]

12/7/2010 3:46:28 PM

what do the brackets around the table name ([user]) do?

12/7/2010 3:51:09 PM

You put brackets around a table or column name if it contains a special character like a hyphen or whatever so SQL Server doesn't shit the bed.

12/7/2010 3:54:21 PM

oh.

12/7/2010 3:57:33 PM

^^^^ throw a LIMIT 1 on the subquery. if that doesn't work, you might have multiple entries in the type table with the same level id

12/7/2010 4:06:16 PM

SELECT u.name, (SELECT tt.type FROM type tt WHERE tt.level=MIN(t.level) LIMIT 1) FROM user u
JOIN relationship r ON (u.id=r.user_id)
JOIN type t ON (r.type_id=t.id)
GROUP BY u.id

qntmfred is using one too many subqueries

[Edited on December 7, 2010 at 4:18 PM. Reason : .]

[Edited on December 7, 2010 at 4:19 PM. Reason : u.id not u.name]

12/7/2010 4:17:42 PM

i feel like this might work but have no way to test it

select user.name,type.type, min(type.level)
from user left join outer join relationship on user.id = relationship.user_id left outer join type on relationship.type_id = type.id
group by user.name,type.type


so I'm assuming by the presence of the relationship table that users need the ability to have multiple roles assigned to them...is that true? If not then I'd wipe out the relationship table and store their types (by ID) in the user table


[Edited on December 7, 2010 at 4:31 PM. Reason : V just tried it and got duplicate rows (same thing as the OP)]

12/7/2010 4:18:14 PM

Quote :

"select user.name,type.type, min(type.level) from user left join outer join relationship on user.id = relationship.user_id left outer join type on relationship.type_id = type.id group by user.name,type.type"

While it may seem that this would work, putting MIN(table.column), table.column2 in a select doesn't actually ensure that table.column2 will be thecolumn2 that's on the same row as column.

12/7/2010 4:21:37 PM

denormalize the data by storing the max title on the user table

and create an index to match

12/7/2010 4:22:43 PM